How can i access the elements from multi dimensional json array (in SQL Server)

Question

I have a multidimensional JSON array, I am accessing the JSON array in SQL Server and using 'OPENJSON' to convert JSON data to SQL. I am currently facing problem in fetching the data from multidimensional array

Declare @Json nvarchar(max)

Set @Json= '[{
    "id": 0,
    "healthandSafety": "true",
    "estimationCost": "7878",
    "comments": "\"Comments\"",
    "image": [{
        "imageData": "1"
    }, {
        "imageData": "2"
    }, {
        "imageData": "3"
    }, {
        "imageData": "4"
    }, {
        "imageData": "5"
    }]
}, {
    "id": 1,
    "healthandSafety": "false",
    "estimationCost": "90",
    "comments": "\"89089\"",
    "image": [{
        "imageData": "6"
    }, {
        "imageData": "7"
    }, {
        "imageData": "8"
    }, {
        "imageData": "9"
    }, {
        "imageData": "10"
    }, {
        "imageData": "11"
    }]
}]'

Select ImageJsonFile from OPENJSON (@Json) with (ImageJsonFile nvarchar(max) '$.image[0].imageData')

When I tried the above code I obtained the following output:

ImageJsonFile
1
6

The output what I am expecting :

ImageJsonFile
1
2
3
4
5

Piotr Palka · Accepted Answer · 2019-06-26 14:47:48Z

2

You need to define query path:

Select * from OPENJSON (@Json,'$[0].image') with (ImageJsonFile nvarchar(max) '$.imageData')

answered Jun 26, 2019 at 14:47

Piotr Palka

3,2351 gold badge12 silver badges20 bronze badges

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

1 Comment

Thom A Over a year ago

That's how you do it! Thanks Piotr, i learned something here. :)

Gottfried Lesigang · Accepted Answer · 2019-06-26 15:59:20Z

You've got an answer already, so this is just to add some more details:

The following will bring back all data from your multi dimensional array, not just one array index you'd have to specify explictly.

DECLARE @Json NVARCHAR(MAX)=
N'[{
    "id": 0,
    "healthandSafety": "true",
    "estimationCost": "7878",
    "comments": "\"Comments\"",
    "image": [{
        "imageData": "1"
    }, {
        "imageData": "2"
    }, {
        "imageData": "3"
    }, {
        "imageData": "4"
    }, {
        "imageData": "5"
    }]
}, {
    "id": 1,
    "healthandSafety": "false",
    "estimationCost": "90",
    "comments": "\"89089\"",
    "image": [{
        "imageData": "6"
    }, {
        "imageData": "7"
    }, {
        "imageData": "8"
    }, {
        "imageData": "9"
    }, {
        "imageData": "10"
    }, {
        "imageData": "11"
    }]
}]';

--The query

SELECT A.id
      ,A.healthandSafety
      ,A.estimationCost
      ,A.comments
      ,B.imageData 
FROM OPENJSON(@Json)
WITH(id INT
    ,healthandSafety BIT
    ,estimationCost INT
    ,comments NVARCHAR(1000)
    ,[image] NVARCHAR(MAX) AS JSON ) A
CROSS APPLY OPENJSON(A.[image])
WITH(imageData INT) B;

The result

+----+-----------------+----------------+----------+-----------+
| id | healthandSafety | estimationCost | comments | imageData |
+----+-----------------+----------------+----------+-----------+
| 0  | 1               | 7878           | Comments | 1         |
+----+-----------------+----------------+----------+-----------+
| 0  | 1               | 7878           | Comments | 2         |
+----+-----------------+----------------+----------+-----------+
| 0  | 1               | 7878           | Comments | 3         |
+----+-----------------+----------------+----------+-----------+
| 0  | 1               | 7878           | Comments | 4         |
+----+-----------------+----------------+----------+-----------+
| 0  | 1               | 7878           | Comments | 5         |
+----+-----------------+----------------+----------+-----------+
| 1  | 0               | 90             | 89089    | 6         |
+----+-----------------+----------------+----------+-----------+
| 1  | 0               | 90             | 89089    | 7         |
+----+-----------------+----------------+----------+-----------+
| 1  | 0               | 90             | 89089    | 8         |
+----+-----------------+----------------+----------+-----------+
| 1  | 0               | 90             | 89089    | 9         |
+----+-----------------+----------------+----------+-----------+
| 1  | 0               | 90             | 89089    | 10        |
+----+-----------------+----------------+----------+-----------+
| 1  | 0               | 90             | 89089    | 11        |
+----+-----------------+----------------+----------+-----------+

The idea in short:

We use the first OPENJSON to get the elements of the first level. The WITH clause will name all elements and return the [image] with NVARCHAR(MAX) AS JSON. This allows to use another OPENJSON to read the numbers from imageData, your nested dimension, while the id-column is the grouping key.

Collectives™ on Stack Overflow

How can i access the elements from multi dimensional json array (in SQL Server)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related