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
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?OPENJSONagain, against your new JSON column (address).CROSS APPLY OPENJSON(j1.address) WITH ( address nvarchar(100) '$' ) j2