OPENJSON with Array of Arrays

Question

I have a large JSON structure (array of arrays) consisting of string values (e.g. "1") that identify each section.

How would I utilize OPENJSON to parse all the information correctly?

A subset of the JSON data:

{"products":[
    {"1":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
]}

I've tried numerous variations of the following without any success:

SELECT @json1 = BulkColumn
 FROM OPENROWSET (BULK 'C:\4\test3.json', SINGLE_CLOB) as j

 SELECT product, category, item FROM OPENJSON (@json1, '$.products[0]')
  With (
    product varchar(20),
    category varchar(20),
    item varchar(20)
  )

Does anyone know what I'm doing wrong?

Is there always just one section? What should happen if there's more than one? Are you going to do anything with the section number? Your code is trivially fixed by using $.products[0]."1" instead of $.products[0], but this assumes you only want section 1. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 25, 2019 at 14:08
I have hundreds of sections all formatted the same way. The result set that I get back is just a NULL value. I've tried using AS JSON and saw no difference. — Jim
– Jim, Commented Feb 25, 2019 at 14:18
Thank you Jeroen Mostert - it works! I feel like a fool for not trying that. — Jim
– Jim, Commented Feb 25, 2019 at 14:20

Gottfried Lesigang · Accepted Answer · 2019-02-25 14:42:24Z

1

You did not provide the expected result, but you might try something along these lines:

DECLARE @json NVARCHAR(MAX)=
N'{"products":[
    {"1":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
]}';

SELECT B.*
FROM OPENJSON(@json,N'$.products')
WITH([1] NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.[1])
WITH(product NVARCHAR(100)
    ,category INT
    ,item INT
    ,[location] INT
    ,quantity INT) B;

In cases with multiple sections, where you need the "1" as a column in your result set, you can try this:

DECLARE @json NVARCHAR(MAX)=
N'{"products":[
    {"1":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
    ,{"2":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
]}';

SELECT B.[key] AS NumericObjectName
      ,C.*
FROM OPENJSON(@json,N'$.products') A
CROSS APPLY OPENJSON(A.[value]) B
CROSS APPLY OPENJSON(B.[value])
WITH(product NVARCHAR(100)
    ,category INT
    ,item INT
    ,[location] INT
    ,quantity INT) C

edited Feb 25, 2019 at 14:42

answered Feb 25, 2019 at 14:32

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

Jim Over a year ago

Thank you Shnugo - nice answer. I tried many times to get CROSS APPLY to work with my queries and could see what I did wrong. thanks for explaining.

Jim Over a year ago

Yes, this is exactly what I have been trying to achieve with my result set.

Collectives™ on Stack Overflow

OPENJSON with Array of Arrays

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related