Correct way to use JSON_Query() with an IN operator

Question

I wanted to create a table using PIVOT operator in SQL Server by extracting data from a JSON object using JSON_QUERY(). But when I use JSON_QUERY() inside IN operator, syntax error is thrown.

DECLARE @DataJson NVARCHAR(MAX) = '{"widgets":{"key1":{"value":[{"text":"TEXT"}]},"key2":{"value":[{"text":"TEXT"}]}}}'

SELECT * FROM OPENJSON(WIT.[DataJsonWidgets]) AS WC  
                PIVOT                   
                (                               
                    MAX(WC.[value]) FOR WC.[key] IN 
                    (                                        
                     JSON_QUERY(@DataJson,'$.widgets.key1') AS key1,         
                     JSON_QUERY(@DataJson,'$.widgets.key2') AS key2        
                    )                            
                ) AS PivotTable

Guess what? MySQL is a totally different product to SQL Server - do yourself a favour and correct your tags so the right experts look at your post. — Dale K, Nov 24 '22 at 06:59
What are you trying to do? Convert attributes to columns? Retrieve the first array item along with ... its grandparent? Flatten the JSON into an EAV schema? The `IN` in the PIVOT operation is used to specify which actual values should become columns, not extract data. Even if that worked, the PIVOT operation would return columns named `key1` and `key2`, not `key` — Panagiotis Kanavos, Nov 24 '22 at 07:08
I'm sure it's just an "editing error", but the JSON snippet is not valid. key1's `value` property is missing its leading quote. — AlwaysLearning, Nov 24 '22 at 07:22
What is that you are trying to achieve here? What is your expected result set? The items inside the pivot's `IN` are meant to be literals (literal column names, actually), so you'll likely be getting a syntax error on the `(` character after the first `JSON_QUERY`. — AlwaysLearning, Nov 24 '22 at 07:27

score 0 · Answer 1 · answered Nov 25 '22 at 08:10

It seems like you're wanting to output a result set with columns named key1 and key2, but your PIVOT syntax isn't correct. Rewriting your PIVOT like this:

DECLARE @DataJson NVARCHAR(MAX) = '{"widgets":{"key1":{"value":[{"text":"TEXT"}]},"key2":{"value":[{"text":"TEXT"}]}}}';

SELECT *
FROM (
  SELECT [key], [value]
  FROM OPENJSON(@DataJson, '$.widgets')
) Src
PIVOT (MAX([value]) FOR [key] IN ([key1], [key2])) Pvt;

Yields the output:

key1	key2
{"value":[{"text":"TEXT"}]}	{"value":[{"text":"TEXT"}]}

Correct way to use JSON_Query() with an IN operator

1 Answers1