When processing JSON using SQL, is there an easy way to process a string containing multiple chunks of JSON

Question

When processing JSON using SQL, is there an easy way to process a string containing multiple chunks of JSON

Greg Booth 1,536

I am trying to process some files that contain JSON. I am importing the file contents into a table (using SSIS - import column) - one row in the table per file - so the JSON for each file is held in a column.
I was expecting that the JASON in each file was just that - some JSON - for example { "label1":"value1",..."labeln":"valuen"} etc - so that this value could be read into a variable (in T-SQL) and then the JSON processed using SELECT * FROM OpenJson(@json ) etc.
However what the file contains is
[ {first bit of json},[2nd bit of json},{ nth bit of json} ]
i.e. any number of bits of json seperated by comma, all enclosed by [ ]

I can remove the [ ] easily - and i could try splitting out the json bits using { as a string delimiter - but there is a risk that there is a { character in the actual json.

As far as i know SQL (we are using sql 2016) JSON processing wont help my parse a sting with multiple comma seperated json strings as described.

Can anyone else see a simpler way of processing strings that contain multiple bits of json ?

Yitzhak Khabinsky 27,196 Reputation points

2022-01-27T18:45:38.857+00:00

@Greg Booth ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
LiHong-MSFT 10,061 Reputation points

2022-01-28T06:38:43.677+00:00

Hi @Greg Booth
Have you validate Viorel's answer and provide any update?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer accepted by question author

0 additional answers

Your answer

Yitzhak Khabinsky 27,196 Reputation points

2022-01-27T18:45:38.857+00:00

@Greg Booth ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
LiHong-MSFT 10,061 Reputation points

2022-01-28T06:38:43.677+00:00

Hi @Greg Booth
Have you validate Viorel's answer and provide any update?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer 1

Viorel 127K

I think that it is a valid JSON, because '[ ]' denotes an array.

Check an example of processing arrays:

declare @json varchar(max) = '[ { "labelA1": "valueA1", "labelA2": "valueA2" }, { "labelB1": "valueB1", "labelB2": "valueB2" } ]'

select r.[key] as [label], r.[value]
from openjson(@json) a
cross apply openjson(a.value) r

/*
Result:

label     value
-------------------
labelA1    valueA1
labelA2    valueA2
labelB1    valueB1
labelB2    valueB2
*/

It depends on what result do you expect from your data.

Greg Booth 1,536 Reputation points

2022-01-27T18:34:09.18+00:00

Thanks Viorel - thats brilliant.
Happy to mark as answer, but if i could ask a follow up question:

Our data will be like
'[ { "id": "1", "labelB": "valueB1" }, { "id": "2", "labelB": "valueB2" } ]'

So the data i will get out from the query would be
label value
id 1
labelB valueB1
id 2
labelB valueB2

But i need to know which rows came with which id (for example have the id as another column in the output) - i.e. need to know that valueB1 is assocated with the data set with id = 1 etc
Is there a way of doing this ?
Kind regards

Viorel 127K

To obtain this output:

/*
id   labelB
------------
1    valueB1
2    valueB2
*/

try this query:

declare @json varchar(max) = '[ { "id": "1", "labelB": "valueB1" }, { "id": "2", "labelB": "valueB2" } ]'

select id, labelB
from openjson(@json)
with ( id int, labelB varchar(max) )

Greg Booth 1,536 Reputation points

2022-01-28T09:59:57.19+00:00

Thank you!

Share via

When processing JSON using SQL, is there an easy way to process a string containing multiple chunks of JSON

0 additional answers

Your answer