Read an array of values from JSON in SQL Server 2016

Asked Jul 24 '23 at 07:53

Active Jul 24 '23 at 08:08

Viewed 29 times

I have a JSON string like below:

[
    {
        "name": "foo",
        "address": [
            "address1",
            "address2"
        ]
    },
    {
        "name": "bar",
        "address": [
            "address3"
        ]
    }
]

I wish to process it to the below 3 rows for insertion in my table:

Name    Address
foo     address1
foo     address2
bar     address3

How can I accomplish that using SQL Server 2016?

I have tried the below:

DECLARE @json nvarchar(max) = '[{"name":"foo","address":["address1","address2"]},{"name":"bar","address":["address3"]}]'

SELECT 
    [name],
    [address]
FROM OpenJson(@json) WITH
(
    [name] nvarchar(20),
    [address] nvarchar(max) as json
)

Which gives me 2 rows with arrays:

name address
foo ["address1","address2"]
bar ["address3"]

I saw solutions handling arrays of objects, but they don't seem to work for me when it's arrays of values

edited Jul 24 '23 at 08:08

asked Jul 24 '23 at 07:53

Mr. Blonde

Have you had a look at `OPENJSON`? What have you tried? Why didn't it work, or what about the existing answers/documentation/articles didn't you understand about it's useage? – Thom A Jul 24 '23 at 07:56
@ThomA See my edit with what I have tried – Mr. Blonde Jul 24 '23 at 08:06
3

Now just use `OPENJSON` again, against your new JSON column (`address`). – Thom A Jul 24 '23 at 08:07
3

Like this https://dbfiddle.uk/cAMZdwbI – Martin Smith Jul 24 '23 at 08:09
@MartinSmith Thank you. It works perfectly as intended. – Mr. Blonde Jul 24 '23 at 08:13
1

`CROSS APPLY OPENJSON(j1.address) WITH ( address nvarchar(100) '$' ) j2` – Charlieface Jul 24 '23 at 12:06

Read an array of values from JSON in SQL Server 2016

0 Answers0