如何使用OPENJSON将JSON列表与SQL Server中的表联接? [英] How to join json list with a table in SQL Server using OPENJSON?

问题描述

我有json数据，其中包含productDetails列表.我需要将每个细节与productId结合起来.

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;

从上面的查询中，我将获得产品列表作为表格.现在，我需要将json中的详细信息与相应的产品

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 ??????);

如何通过比较@detailJSON中的productName和表@products来获取productId?

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

推荐答案

只需要一个简单的内部联接

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 

这篇关于如何使用OPENJSON将JSON列表与SQL Server中的表联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！