Question 110916 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

SQL Server : parsing a JSON column with irregular values

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.

Tags:

3

Answers


  1. 0

    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));
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search