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

Add two different column values as an attribute into the the already JSON formated column which already have data. SQL Server

Existing Data

{ "Attr1": "AAAAAAAAA", "Attr2": 70, "Attr3": null, "Attr4": false }

I want to insert in below format right after the Attr2
both exists

"NewAttr": { "SourceTable1.Column1": "12xx12", "SourceTable2.Column2": "0192xx" }

Column1 exists but not Column2

"NewAttr": { "SourceTable1.Column1": "12xx12" }

Column2 exists but not Column1

"NewAttr": { "SourceTable2.Column2": "0192xx" }

None exist

"NewAttr": {}

SELECT top 10000 JSON_MODIFY(dt.DestinationColumn, '$.NewAttr', st.Column1) 
FROM dbo.Joiningtable jt 
INNER JOIN dbo.DestinationTable dt ON dt.ID = jt.TID 
INNER JOIN dbo.SourceTable st ON jt.TID = st.ID

I am not able to get the point to add nested attr as I showed in example.

I was trying to write the first select statement so that I can see results are correctly append. but as a final result, I need to update DestinationTable.DestinationColumn

Tags:

2

Answers


  1. 0

    You could either use nested JSON_MODIFY calls:

    SELECT
  JSON_MODIFY(
    JSON_MODIFY(
      JSON_MODIFY(
        dt.DestinationColumn,
        '$.NewAttr',
        JSON_QUERY('{}')
      ),
      '$.NewAttr."SourceTable.Column1"',
      st.Column1
    ),
    '$.NewAttr."SourceTable.Column2"',
    st.Column2
  )
FROM dbo.Joiningtable jt 
INNER JOIN dbo.DestinationTable dt on dt.ID = jt.TID 
INNER JOIN dbo.SourceTable st on jt.TID = st.ID;

    Or you could generate the whole nested object in an APPLY and insert it at once:

    SELECT
  JSON_MODIFY(dt.DestinationColumn, '$.NewAttr', JSON_QUERY(j.NewAttr))
FROM dbo.Joiningtable jt 
INNER JOIN dbo.DestinationTable dt on dt.ID = jt.TID 
INNER JOIN dbo.SourceTable st on jt.TID = st.ID
OUTER APPLY (
    SELECT
      [SourceTable.Column1] = st.Column1,
      [SourceTable.Column2] = st.Column2
    WHERE st.Column1 IS NOT NULL OR st.Column2 IS NOT NULL
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(NewAttr);

    db<>fiddle

    Login or Signup to reply.
  2. 0

    I used nested case statements

    CREATE TABLE SourceTable
(
  ID int,
  Column1 nvarchar(max),
  Column2 nvarchar(max)
)  
  
CREATE TABLE DestinationTable
(
  ID int,
  DestinationColumn nvarchar(max)
)



CREATE TABLE Joiningtable
(
  TID int
)    


INSERT INTO   SourceTable (ID, Column1,Column2) Values
  (1,'12xx12','0192xx'),
  (2,'12xx12',NULL),
  (3,NULL,'0192xx'),
  (4,NULL,NULL);

INSERT INTO   DestinationTable (ID, DestinationColumn) Values
  (1,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}'),
  (2,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}'),
  (3,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}'),
  (4,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}');

INSERT INTO  Joiningtable (TID) Values 
  (1),
  (2),
  (3),
  (4);

  
SELECT * FROM SourceTable
SELECT * FROM DestinationTable
SELECT * FROM Joiningtable
  
SELECT top 10000 
  CASE  WHEN st.Column1 IS NULL THEN   CASE  WHEN st.Column2 IS NULL THEN        'Both empty'     ELSE       'Only Col 2 exists'     END
  ELSE  CASE  WHEN st.Column2 IS NULL THEN 'Only Col 1 exists' ELSE 'Both Col 1 and 2 exists' END
  END as scenario,

  CASE  WHEN st.Column1 IS NULL THEN 
    CASE  WHEN st.Column2 IS NULL THEN  
       JSON_MODIFY(dt.DestinationColumn, '$.NewAttr',JSON_QUERY('{}'))
    ELSE 
        JSON_MODIFY(dt.DestinationColumn,'$.NewAttr','SourceTable.Column2": "' + st.Column2)     
    END
  ELSE  
    CASE  WHEN st.Column2 IS NULL THEN 
      JSON_MODIFY(dt.DestinationColumn,'$.NewAttr','SourceTable.Column1": "' + st.Column1) 
    ELSE 
       JSON_MODIFY(dt.DestinationColumn,'$.NewAttr','SourceTable.Column1": "' + st.Column1 + '":SourceTable.Column2": "' + st.Column2)  
    END
  END as DestinationColumn
  
FROM dbo.Joiningtable jt 
INNER JOIN dbo.DestinationTable dt on dt.ID = jt.TID 
INNER JOIN dbo.SourceTable st on jt.TID = st.ID

    fiddle

    scenario DestinationColumn
    Col1 & Col2 {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":"SourceTable.Column1": "12xx12":SourceTable.Column2": "0192xx"}
    Only Col1 {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":"SourceTable.Column1": "12xx12"}
    Only Col2 {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":"SourceTable.Column2": "0192xx"}
    Both empty {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{}}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search