How to join json list with a table in SQL Server using OPENJSON?

Question

I have json data which have a list of productDetails. I need to combine each of the detail with productId.

I am using a stored procedure. This is my code:

DECLARE @products AS TABLE(productName NVARCHAR(255), 
                           productId INT NOT NULL);

INSERT INTO @products  
    EXEC [saveproducts] @product;

From the above query, I will get the list of products as a table. Now I need to join the detail in the json with corresponding product

INSERT INTO @detail 
     SELECT
         [detaiId], [productId]
     FROM 
         OPENJSON(@detailJSON) 
     WITH(detailId UNIQUEIDENTIFIER 'strict $.detaiId',
       productId INT ??????);

How to get the productId by comparing the productName in @detailJSON and from table @products?

Can you provide JSON sample (structure) and productDetails schema — Habeeb
– Habeeb, Commented Jan 2, 2017 at 11:30
@Habeeb.. I have solved it actually only a simle inner join is needed. Thanks for the edit and responce — S M
– S M, Commented Jan 2, 2017 at 12:03

Tom Robinson · Accepted Answer · 2017-09-22 14:14:58Z

14

Only a simple inner join is needed

 INSERT INTO @detail 
 SELECT
     J.[detaiId], P.[productId]
 FROM 
 @products P
 INNER JOIN
     OPENJSON(@detailJSON) 
 WITH(detailId UNIQUEIDENTIFIER 'strict $.detaiId',
      productName  NVARCHAR(255) 'strict $.productName'
   ) J 
 ON J.productName = P.productName

edited Sep 22, 2017 at 14:14

Tom Robinson

8,5489 gold badges63 silver badges103 bronze badges

answered Jan 2, 2017 at 12:06

S M

3,2536 gold badges35 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

How to join json list with a table in SQL Server using OPENJSON?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related