Extract values from JSON of Array in T-SQL

Question

I have a JSON like this to process in SQL

{"RowIndex":[1,2], "Data":["a","b"]}

and i want to extract the data to show that as a table like this

RowIndex    Data
  1           a
  2           b

I understand that i have to use OPENJSON, JSON_QUERY or JSON_VALUE but i cannot find a way to get what I want that not implies to write a query with many join like

select  C1.value as RowIndex,
        C2.value as Data,
From (select [key], value from OPENJSON(JSON_QUERY(@jsonstring, '$.RowIndex'))) C1 
inner join (select [key], value from OPENJSON(JSON_QUERY(@jsonstring, '$.Data'))) C2 on C1.[key] = C2.[key]

Because if the arrays in the JSON grow the query will be unmaintenable and slow

Thom A · Accepted Answer · 2020-07-29 15:48:13Z

1

One method, using a "couple" of OPENJSON clauses:

DECLARE @JSON nvarchar(MAX) = N'{"RowIndex":[1,2], "Data":["a","b"]}';

SELECT RI.[value] AS RowIndex,
       D.[value] AS Data
FROM OPENJSON(@JSON)
     WITH (RowIndex nvarchar(MAX) AS JSON,
           Data nvarchar(MAX) AS JSON) J
     CROSS APPLY OPENJSON(RowIndex) RI
     CROSS APPLY OPENJSON(Data) D
WHERE RI.[key] = D.[key];

To elaborate on my comments though, it seems like you should be fixing the JSON design and have something like this:

[
    {
        "RowIndex": "1",
        "Data": "a",
        "Number": "1"
    },
    {
        "RowIndex": "2",
        "Data": "b",
        "Number": "3"
    }
]

Which can be far more easily queried:

DECLARE @JSON nvarchar(MAX) = N'[
    {
        "RowIndex": "1",
        "Data": "a",
        "Number": "1"
    },
    {
        "RowIndex": "2",
        "Data": "b",
        "Number": "3"
    }
]';

SELECT *
FROM OPENJSON(@JSON)
     WITH (RowIndex int,
           Data char(1),
           Number int) OJ;

edited Jul 29, 2020 at 15:48

answered Jul 29, 2020 at 15:08

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sethlans Over a year ago

If I understand well, if I have 3 arrays there will be 3 OPENJSON and so on

Thom A Over a year ago

Correct, @MattiaGiacone , and more clauses in your WHERE. If that is the case, however, then it seems the real problem is the JSON and your JSON should look more like this (added an extra node, Number): [{"RowIndex": "1","Data": "a","Number":"1"},{"RowIndex": "2","Data": "b","Number":"3"}]

Sethlans Over a year ago

It will be better if I can generate the JSON like {[{ "RowIndex":0, "Data":"a" },{ "RowIndex":1, "Data":"b" }]} to perform a query like

SELECT    JSON_VALUE([value], '$.RowIndex') AS RowIndex,    JSON_VALUE([value], '$.Data') AS Data, FROM (SELECT [key],[value],[type] FROM OPENJSON(@jsonstring) )

Thom A Over a year ago

Why "spam" JSON_VALUE @MattiaGiacone? It's slower (I confirmed this earlier today in another answer).

Sethlans Over a year ago

yes that's better. The real Json has 12 array and for 250 element with the 12 cross apply it takes around 1 second. But if i create the json in the other way the query is almost instantaneous

|

Collectives™ on Stack Overflow

Extract values from JSON of Array in T-SQL

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related