Question 109294 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Convert a JSON Object into an SQL table on SQL Server 2019

I am trying to convert a JSON object with "keys" as columns and each value in the list as a row.

I have managed to create the table structure I want, however I am getting the wrong number of rows.

DECLARE @result NVARCHAR(MAX) = N'{"foo": [101, 202, 303], "bar": [1,2,3], "test": [10,20,30], "input": [100,200,300]}'

SELECT
    foo.value AS foo,
    bar.value AS bar,
    test.value AS test,
    input.value AS input
FROM 
    OPENJSON(@result)
    WITH (
        foo NVARCHAR(MAX) '$.foo' AS JSON,
        bar NVARCHAR(MAX) '$.bar' AS JSON,
        test NVARCHAR(MAX) '$.test' AS JSON,
        input NVARCHAR(MAX) '$.input' AS JSON
    ) AS json
CROSS APPLY OPENJSON(foo) AS foo
CROSS APPLY OPENJSON(bar) AS bar
CROSS APPLY OPENJSON(test) AS test
CROSS APPLY OPENJSON(input) AS input

This outputs 81 rows of this data:

foo bar test input
101 1 10 100
101 1 10 100
202 2 20 200
202 2 20 200
303 3 30 300
303 3 30 300

x 27 rows each for 101 202 and 303 column rows values respectively

I want it to return rows that are length of the values in the list which would be 3 rows in this case:

Expected:

foo bar test input
101 1 10 100
202 2 20 200
303 3 30 300

I have tried CROSS APPLY and OUTER APPLY and both give the same output.

Tags:

2

Answers


  1. 0

    Here is one way to do it without exploding each value by repeating the CROSS APPLY against each column. I’m sure other JSON experts can write something less verbose.

    ;WITH src AS
(
  SELECT rn = y.[key], x.[key], y.value
  FROM OPENJSON(@result) AS x
  CROSS APPLY OPENJSON(value) AS y
)
SELECT [foo],[bar],[test],[input] 
FROM
(
  SELECT rn, [key], value = MAX(value) 
  FROM src GROUP BY rn, [key]
) AS d 
  PIVOT (MAX(value) FOR [key] IN 
  ([foo],[bar],[test],[input])) AS p;

    Results:

    foo bar test input
    101 1 10 100
    202 2 20 200
    303 3 30 300

    This adapts easily to any number of values in the array, even non-matching. Example.

    Login or Signup to reply.
  2. 0

    You just need a WHERE at the bottom of your query to match the array indexes.

    WHERE foo.[key] = bar.[key]
  AND foo.[key] = test.[key]
  AND foo.[key] = input.[key];

    db<>fiddle

    Admittedly this only works neatly when all the arrays are the same length.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search