SQL Server : parsing a JSON column with irregular values-CodePudding

I have SQL Server 2016 (v13) installation where I am trying to parse a column with JSON data. The data in the column RequestData is in the following format:

[
    { "Name": "SourceSystem", "Value": "SSValue" },
    { "Name": "SourceSystemId", "Value": "XYZ" }
]
[
    { "Name": "SourceSystemId", "Value": "SSID" },
    { "Name": "SourceSystem", "Value": "SSVALUE2" }
]

What I need to get are the values for the SourceSystem element of the JSON object in each row. And here is my Select statement:

SELECT TOP 2 
    JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem 
FROM 
    RequestDetail

But, due to the order of the JSON elements in the column's data, the values being returned for the SourceSystem column are not correct.

SSValue, SSID

Please note, I need to be able to parse the JSON elements so that the SourceSystem column will have correct values, i.e SSValue and SSValue2.

I have also tried JSON_Query using some online examples but no luck so far.

Thank you!

Edit The Question has been modified by someone after I posted, so I am adding this for clarification: Each row of data, as given in the Question, will have several 'Name' elements and those Name elements can be SourceSystem or SourceSystemId. The Question shows data from two rows from the database table's column, but, as you can see, the SourceID and SourceSystemId elements in each row are not in the same order between the first and the second row. I simply need to parse SourceSystem element per row.

CodePudding user response：

Using openjson, to get all the data in columns you can use it as any othe table

SELECT
Value
FROM RequestDetail
     CROSS APPLY OPENJSON(RequestDetail.RequestData)
                 WITH (Name nvarchar(20),
  Value nvarchar(20))
WHERE Name = 'SourceSystem';

Value
SSValue
SSVALUE2

fiddle

CodePudding user response：

Presumably you need OPENJSON here, not JSON_VALUE:

SELECT *
FROM (VALUES(N'[{"Name":"SourceSystem","Value":"SSValue"},{"Name":"SourceSystemId","Value":"XYZ"}]'),
            (N'[{"Name":"SourceSystemId","Value":"SSID"},{"Name":"SourceSystem","Value":"SSVALUE2"}]'))V(YourJSON)
     CROSS APPLY OPENJSON(V.YourJSON)
                 WITH (Value nvarchar(20));

CodePudding user response：

When you want to use JSON_VALUE, just select the correct (needed) values:

SELECT 
   JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem
FROM RequestDetail

UNION ALL

SELECT 
   JSON_VALUE(RequestData, '$[1].Value') AS SourceSystem
FROM RequestDetail

output:

SourceSystem
SSValue
SSID
XYZ
SSVALUE2

When you only need values from "SourceSystem", you can always do:

SELECT SourceSystem
FROM (
   SELECT 
      JSON_VALUE(RequestData, '$[0].Name') AS Name,
      JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem
   FROM RequestDetail

   UNION ALL

   SELECT 
      JSON_VALUE(RequestData, '$[0].Name') AS Name,
      JSON_VALUE(RequestData, '$[1].Value') AS SourceSystem
   FROM RequestDetail )x
WHERE Name='SourceSystem';

output:

SourceSystem
SSValue
XYZ

see: DBFIDDLE

EDIT:

SELECT 
    x,
    MIN(CASE WHEN Name='SourceSystem' THEN SourceSystem END) as SourceSystem,
    MIN(CASE WHEN Name='SourceSystemId' THEN SourceSystem END) as SourceSystemId
FROM (
   SELECT
      ROW_NUMBER() OVER (ORDER BY RequestData) as x,
      JSON_VALUE(RequestData, '$[0].Name') AS Name,
      JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem
   FROM RequestDetail

   UNION ALL

   SELECT 
      ROW_NUMBER() OVER (ORDER BY RequestData) as x,
      JSON_VALUE(RequestData, '$[1].Name') AS Name,
      JSON_VALUE(RequestData, '$[1].Value') AS SourceSystem
   FROM RequestDetail 
)x
GROUP BY x
;

This will give:

x	SourceSystem	SourceSystemId
1	SSValue	XYZ
2	SSVALUE2	SSID