Assistance with TSQL OPENJSON extracting metadata and data into table

Question

I have this JSON:

{
    "environments": [
        {
            "environment": "Development",
            "customs": {
                "FieldA": "FieldA_Dev",
                "FieldB": "FieldB_Dev",
                "FieldC": "FieldC_Dev"
            }
        },
        {
            "environment": "Test",
            "customs": {
                "FieldA": "FieldA_Test",
                "FieldB": "FieldB_Test",
                "FieldC": "FieldC_Test"
            }
        },
        {
            "environment": "Production",
            "customs": {
                "FieldA": "FieldA_Prod",
                "FieldB": "FieldB_Prod",
                "FieldC": "FieldC_Prod"
            }
        }
    ],
    "customMetadata": [
        {
            "name": "FieldA",
            "default": "FieldA_Default",
            "description": "FieldA_Description"
        },
        {
            "name": "FieldB",
            "default": "FieldB_Default",
            "description": "FieldB_Description"
        },
        {
            "name": "FieldC",
            "default": "FieldC_Default",
            "description": "FieldC_Description"
        }
    ]
}

I am trying to build a SQL table like this:

CREATE TABLE #TempTable 
(
    [Name] NVARCHAR(255),
    [Default] NVARCHAR(255),
    [Description] NVARCHAR(255),
    [Development] NVARCHAR(255),
    [Test] NVARCHAR(255),
    [Production] NVARCHAR(255)
);

And then map the values from the JSON file.

I can do the initial metadata insert with:

INSERT INTO #TempTable ([Name], [Default], [Description], [Development], [Test], [Production])
SELECT 
    [name] AS [Name],
    [default] AS [Default],
    [description] AS [Description],
    NULL AS [Development],
    NULL AS [Test],
    NULL AS [Production]
FROM 
    OPENJSON(@json, '$.customMetadata') 
    WITH (
        [name] NVARCHAR(255) '$.name',
        [default] NVARCHAR(255) '$.default',
        [description] NVARCHAR(255) '$.description'
    );

But afterwards to map each of the environments' values into the table, I get stuck.

Please help.

I have tried with OPENJSON and CROSS APPLY JSON, and AI tool assistance, but just cannot get it right.

Answer 1

i made as 2 separate select statements . just to explain how to bound data:

working example here Also, this post should give you more on subject:

SQL
declare @json nvarchar (max) = '{
    "environments": [
        {
            "environment": "Development",
            "customs": {
                "FieldA": "FieldA_Dev",
                "FieldB": "FieldB_Dev",
                "FieldC": "FieldC_Dev"
            }
        },
        {
            "environment": "Test",
            "customs": {
                "FieldA": "FieldA_Test",
                "FieldB": "FieldB_Test",
                "FieldC": "FieldC_Test"
            }
        },
        {
            "environment": "Production",
            "customs": {
                "FieldA": "FieldA_Prod",
                "FieldB": "FieldB_Prod",
                "FieldC": "FieldC_Prod"
            }
        }
    ],
    "customMetadata": [
        {
            "name": "FieldA",
            "default": "FieldA_Default",
            "description": "FieldA_Description"
        },
        {
            "name": "FieldB",
            "default": "FieldB_Default",
            "description": "FieldB_Description"
        },
        {
            "name": "FieldB",
            "default": "FieldB_Default",
            "description": "FieldB_Description"
        }
    ]
}'
  
  
SELECT 
    rn = ROW_NUMBER() over (order by (select 1)),
    [name] AS [Name],
    [default] AS [Default],
    [description] AS [Description],
    NULL AS [Development],
    NULL AS [Test],
    NULL AS [Production]
FROM 
    OPENJSON(@json, '$.customMetadata') 
    WITH (
        [name] NVARCHAR(255) '$.name',
        [default] NVARCHAR(255) '$.default',
        [description] NVARCHAR(255) '$.description'
    )


SELECT 
    rn = ROW_NUMBER() over (order by (select 1)),
    *
FROM 
    OPENJSON(@json, '$.environments') 
    WITH (
        environment NVARCHAR(255) '$.environment',
        FieldA NVARCHAR(255) '$.customs.FieldA',
        FieldB NVARCHAR(255) '$.customs.FieldB',
        FieldC NVARCHAR(255) '$.customs.FieldC'
    )

first table (yours):

rn	Name	Default	Description	Development	Test	Production
1	FieldA	FieldA_Default	FieldA_Description	NULL	NULL	NULL
2	FieldB	FieldB_Default	FieldB_Description	NULL	NULL	NULL
3	FieldB	FieldB_Default	FieldB_Description	NULL	NULL	NULL

second table -(what you are looking for)

rn	environment	FieldA	FieldB	FieldC
1	Development	FieldA_Dev	FieldB_Dev	FieldC_Dev
2	Test	FieldA_Test	FieldB_Test	FieldC_Test
3	Production	FieldA_Prod	FieldB_Prod	FieldC_Prod

so simple inner join will do trick:

 SELECT 
    FIRST_TABLE.*,
    SECOND_TABLE.*
 FROM
 (SELECT 
    rn = ROW_NUMBER() over (order by (select 1)),
    [name] AS [Name],
    [default] AS [Default],
    [description] AS [Description]
FROM 
    OPENJSON(@json, '$.customMetadata') 
    WITH (
        [name] NVARCHAR(255) '$.name',
        [default] NVARCHAR(255) '$.default',
        [description] NVARCHAR(255) '$.description'
    )
) FIRST_TABLE
INNER JOIN (
SELECT 
    rn = ROW_NUMBER() over (order by (select 1)),
    environment,
    FieldA,FieldB,FieldC
FROM 
    OPENJSON(@json, '$.environments') 
    WITH (
        environment NVARCHAR(255) '$.environment',
        FieldA NVARCHAR(255) '$.customs.FieldA',
        FieldB NVARCHAR(255) '$.customs.FieldB',
        FieldC NVARCHAR(255) '$.customs.FieldC'
    )
) SECOND_TABLE on FIRST_TABLE.rn = SECOND_TABLE.rn

rn	Name	Default	Description	rn	environment	FieldA	FieldB	FieldC
1	FieldA	FieldA_Default	FieldA_Description	1	Development	FieldA_Dev	FieldB_Dev	FieldC_Dev
2	FieldB	FieldB_Default	FieldB_Description	2	Test	FieldA_Test	FieldB_Test	FieldC_Test
3	FieldB	FieldB_Default	FieldB_Description	3	Production	FieldA_Prod	FieldB_Prod	FieldC_Prod

Answer 2

You did not explicitly state your expected output.
Furthermore, your JSON repeats FieldB. I'd assume a copy-paste error...

This is my suggestion:

SELECT Cust.[name]
      ,Cust.[default]
      ,Cust.[description]
      ,MAX(CASE WHEN Env.environment='Development' THEN JSON_VALUE(Env.customs, CONCAT('$.',Cust.[name])) END) AS ValueForDev
      ,MAX(CASE WHEN Env.environment='Test'        THEN JSON_VALUE(Env.customs, CONCAT('$.',Cust.[name])) END) AS ValueForTest
      ,MAX(CASE WHEN Env.environment='Production'  THEN JSON_VALUE(Env.customs, CONCAT('$.',Cust.[name])) END) AS ValueForProd
FROM OPENJSON(@json) WITH (environments NVARCHAR(MAX) AS JSON, customMetadata NVARCHAR(MAX) AS JSON) A

CROSS APPLY OPENJSON(A.environments) 
WITH (environment NVARCHAR(250)
     ,customs NVARCHAR(MAX) AS JSON) Env

CROSS APPLY OPENJSON(A.customMetadata)
WITH ([name] NVARCHAR(250)
     ,[default] NVARCHAR(250)
     ,[description] NVARCHAR(250)) Cust

GROUP BY Cust.[name],Cust.[default],Cust.[description];

The result

+--------+----------------+--------------------+-------------+--------------+--------------+
| name   | default        | description        | ValueForDev | ValueForTest | ValueForProd |
+--------+----------------+--------------------+-------------+--------------+--------------+
| FieldA | FieldA_Default | FieldA_Description | FieldA_Dev  | FieldA_Test  | FieldA_Prod  |
| FieldB | FieldB_Default | FieldB_Description | FieldB_Dev  | FieldB_Test  | FieldB_Prod  |
| FieldC | FieldC_Default | FieldC_Description | FieldC_Dev  | FieldC_Test  | FieldC_Prod  |
+--------+----------------+--------------------+-------------+--------------+--------------+

The idea in short:

• The FROM uses OPENJSON to retrieve your main sections AS JSON.
• Then we use CROSS APPLY OPENJSON() to dive into these sections using WITH() to get the content.
• GROUP BY will reduce the output to one line per field.
• The MAX() with CASE WHEN is old-fashioned PIVOT. You can use PIVOT-syntax as well, but this way is more flexible.
• The magic happens in JSON_VALUE() where we can use the field's name in the json path.

Hint: You can use SELECT * FROM ... and take away the GROUP BY to look into the full result set.

If interested, you'd get the same with PIVOT.

SELECT p.*
FROM
(
    SELECT Cust.[name]
          ,Cust.[default]
          ,Cust.[description]
          ,JSON_VALUE(Env.customs, CONCAT('$.',Cust.[name])) AS ValueByFieldName
          ,Env.environment
    FROM OPENJSON(@json) WITH (environments NVARCHAR(MAX) AS JSON, customMetadata NVARCHAR(MAX) AS JSON) A

    CROSS APPLY OPENJSON(A.environments) 
    WITH (environment NVARCHAR(250)
         ,customs NVARCHAR(MAX) AS JSON) Env

    CROSS APPLY OPENJSON(A.customMetadata)
    WITH ([name] NVARCHAR(250)
         ,[default] NVARCHAR(250)
         ,[description] NVARCHAR(250)) Cust
) t
PIVOT
(
    MAX(ValueByFieldName) FOR environment IN(Development, Test, Production)
) p