CODEWITHSUNDEEP
arraysjsont-sql
Sethlans
Sethlans

Reputation: 417

Extract values from JSON of Array in T-SQL

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

Upvotes: 0

Views: 252

Answers (1)

Thom A
Thom A

Reputation: 96013

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;

Upvotes: 1

Related Questions