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?
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With