0

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

6
  • 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? Commented Jul 24, 2023 at 7:56
  • @ThomA See my edit with what I have tried Commented Jul 24, 2023 at 8:06
  • 3
    Now just use OPENJSON again, against your new JSON column (address). Commented Jul 24, 2023 at 8:07
  • 3
    Like this dbfiddle.uk/cAMZdwbI Commented Jul 24, 2023 at 8:09
  • 1
    CROSS APPLY OPENJSON(j1.address) WITH ( address nvarchar(100) '$' ) j2 Commented Jul 24, 2023 at 12:06

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.