How to parse JSON column to a table with TSQL

Question

I am trying to open up a JSON string and have each key to be its own column. The JSON column lives in a Metadata table with key and polymorphic id.

I want to be able to parse every key into its own column with its corresponding value populated for each polymorphic id.

I can parse each key one by one with json_query, but I see there is a function call openjson which can parse the entire string, but I don't know how to use it since the example from the documentation are applying the function on a set value instead of a column from a table. If there a easier way to parse a JSON string than using json_query?

Answer 1

I'd try this approach

SELECT t.RelatedPolimorphicId
      ,t.[Key] 
      ,A.*
FROM YourMetaDataTable t
CROSS APPLY OPENJSON(t.[Value]) 
WITH (
     BrandPresent BIT
    ,OneImage BIT
    ,UPCPresenet BIT
    ,ModelNumberPresent BIT
    ,TitlePresent BIT
    ,DescriptionPresent BIT
    ,Feature1Present BIT
) A;

OPENJSON in connection with a WITH clause offers a nice and clean and type-safe(!) approach to read your JSON. I'd use BIT, because true and false will be translated implicitly.

Answer 2

You may try with the next approach using OPENJSON() and WITH clause (to specify columns and their types). Without WITH clause OPENJSON returns three columns - key, value and type of each {key: value} pair.

Input

CREATE TABLE #Table (
   RelatedPolimorphicId int,
   [Key] nvarchar(50),
   [Value] varchar(max)
)
INSERT INTO #Table
   (RelatedPolimorphicId, [Key], [Value])
VALUES
   (23, N'ContentStats', N'{"BrandPresent": true, "OneImage": true, "UPCPresenet": true, "ModelNumberPresent": true, "TitlePresent": true, "DescriptionPresent": true, "Feature1Present": true}')

Statement

SELECT 
   t.RelatedPolimorphicId,
   j.*
FROM #Table t
CROSS APPLY (
   SELECT * 
   FROM OPENJSON(t.[Value])
   WITH (
       BrandPresent varchar(10) '$.BrandPresent',
       OneImage varchar(10) '$.OneImage',
       UPCPresenet varchar(10) '$.UPCPresenet',
       ModelNumberPresent varchar(10) '$.ModelNumberPresent',
       TitlePresent varchar(10) '$.TitlePresent',
       DescriptionPresent varchar(10) '$.TitlePresent',
       Feature1Present varchar(10) '$.Feature1Present'
   )
) j

Output

RelatedPolimorphicId    BrandPresent    OneImage    UPCPresenet ModelNumberPresent  TitlePresent    DescriptionPresent  Feature1Present
23                      true            true        true        true              true              true            true

Answer 3

Please try this:

SELECT p.RelatedPolymorphId,p.[BrandPresent],p.[OneImage],p.[UPCPresent],p.[ModelNumberPresent],p.[TitlePresent],p.[DescriptionPresent],p.[Feature1Present]
FROM (SELECT v.RelatedPolymorphId,v.Value AS [JsonValue] FROM [YourTableName] v) a
CROSS APPLY OPENJSON(a.JsonValue) j
PIVOT(MAX(j.[value]) FOR j.[key] IN ([BrandPresent],[OneImage],[UPCPresent],[ModelNumberPresent],[TitlePresent],[DescriptionPresent],[Feature1Present])) p
;

Answer 4

Thanks everyone for responding. @shnugo, in your solution with openjson, the solution still have to list every key that I wanted to parse, I have solved it with JSON_query, a simpler solution, but the problem is I have more than 40 keys to parse, which is inefficient to list them one by one. I was looking for a function that can parse the JSON column without having to specify each key within the JSON string.

solution with json_query