OPENJSON on object with two arrays

Question

I have the following json in a column:

{
   "fields":[
      {
         "field":"modelName",
         "value":"abc123"
      },
      {
         "field":"displayName",
         "value":"ABC 123"
      },
      {
         "field":"order",
         "value":5
      }
   ],
   "variables":[
      {
         "varId":4,
         "oldValue":100,
         "newValue":"150"
      },
      {
         "varId":5,
         "oldValue":"abc",
         "newValue":"def"
      }
   ]
}

And I would like to pull this information out to something like the following:

Id  Field        Value    VarId    oldValue    newValue    
2   modelName    abc123   null     null        null
2   displayName  ABC 123  null     null        null
2   order        5        null     null        null
2   null         null     4        100         150
2   null         null     5        abc         def

That way I can just iterate through the result set and just null check to see what type it is.

I currently have the following statement:

select Id, Fields.Field, Fields.Value, Variables.VarId, Variables.OldValue, Variables.NewValue from  Product
cross apply openjson( data, '$.fields') with (Field varchar(50) '$.field', Value varchar(50) '$.value') AS Fields
cross apply openjson( data, '$.variables') with (VarId int '$.varId', OldValue varchar(50) '$.oldValue', NewValue varchar(50) '$.newValue') AS Variables

But it gives me the following output:

As you can see, everything is duplicated. Is it possible to get the output that I wanted?

Thanks

Thom A · Accepted Answer · 2019-10-25 13:54:16Z

2

You'll need to do this as 2 separate parses. This uses a FULL OUTER JOIN with an (admittedly) dumb ON clause. you could also use a UNION ALL and NULL the values in set that doesn't have the columns:

CREATE TABLE dbo.Product (ID int,
                          [data] nvarchar(MAX));

DECLARE @JSON nvarchar(MAX) = N'{
   "fields":[
      {
         "field":"modelName",
         "value":"abc123"
      },
      {
         "field":"displayName",
         "value":"ABC 123"
      },
      {
         "field":"order",
         "value":5
      }
   ],
   "variables":[
      {
         "varId":4,
         "oldValue":100,
         "newValue":"150"
      },
      {
         "varId":5,
         "oldValue":"abc",
         "newValue":"def"
      }
   ]
}';

INSERT INTO dbo.Product (ID,
                         [data])
VALUES(2,@JSON);
GO

WITH Fields AS(
    SELECT P.Id,
           F.Field,
           F.Value,
    FROM Product P
         CROSS APPLY OPENJSON(data, '$.fields')
                     WITH (Field varchar(50) '$.field',
                           [Value] varchar(50) '$.value') F),
Variables AS(
    SELECT P.Id,
           V.VarId,
           V.OldValue,
           V.NewValue
    FROM Product P
    CROSS APPLY OPENJSON(data, '$.variables')
                WITH (VarId int '$.varId',
                      OldValue varchar(50) '$.oldValue',
                      NewValue varchar(50) '$.newValue') V)
SELECT ISNULL(F.ID,V.ID) AS ID,
       F.Field,
       F.[Value],
       V.VarId,
       V.OldValue,
       V.NewValue
FROM Fields F
     FULL OUTER JOIN Variables V ON 1 = 2; --Dumb ON clause is Dumb

answered Oct 25, 2019 at 13:54

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ADringer Over a year ago

Excellent, didn't expect an answer that quick, thanks!

ADringer Over a year ago

One question though, if I wanted to filter on P.Id, where would the WHERE clause go? I tried putting it in the individual parses but couldn't get the syntax correct. I wasn't sure whether to put it in the last select statement. Is SQL clever enough to only grab the ones I want, or would it grab everything in each parser and then filter it out?

Thom A Over a year ago

I would but it in the CTEs, @ADringer . The syntax would simply be WHERE P.ID = 1. WHERE is one of the basics of SQL, so if you aren't familiar with it, I suggest reading up on it.

Zhorov · Accepted Answer · 2019-10-27 09:04:24Z

It's just another possible approach (thanks, @Larnu, for the test data). Of course, you need to parse fields and variables parts separately, but you can use OPENJSON() with one explicit schema (WITH clause):

Table:

CREATE TABLE Product (
   ID int,
   [data] nvarchar(MAX)
);
DECLARE @json nvarchar(MAX) = N'{
   "fields":[
      {
         "field":"modelName",
         "value":"abc123"
      },
      {
         "field":"displayName",
         "value":"ABC 123"
      },
      {
         "field":"order",
         "value":5
      }
   ],
   "variables":[
      {
         "varId":4,
         "oldValue":100,
         "newValue":"150"
      },
      {
         "varId":5,
         "oldValue":"abc",
         "newValue":"def"
      }
   ]
}';

INSERT INTO Product (ID, [data])
VALUES
   (1, @json),
   (2, @json),
   (3, @json)

Statement:

SELECT p.ID, j.*
FROM Product p
CROSS APPLY (
   SELECT *
   FROM OPENJSON (p.data, '$.fields') WITH (
      field varchar(100) '$.field',
      value varchar(100) '$.value',
      varId int '$.varId',
      oldValue varchar(100) '$.oldValue',
      newValue varchar(100) '$.newValue'
   )
   UNION ALL 
   SELECT *
   FROM OPENJSON (p.data, '$.variables') WITH (
      field varchar(100) '$.field',
      value varchar(100) '$.value',
      varId int '$.varId',
      oldValue varchar(100) '$.oldValue',
      newValue varchar(100) '$.newValue'
   )
) j
-- Additional WHERE clause
--WHERE p.ID = 2

Collectives™ on Stack Overflow

OPENJSON on object with two arrays

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related