SQL Server 2016 select where in json array of objects with normal table

Question

Please help me with sql query to fullfill below requirement

I want to join a table with another table having a JSON column with two string column comparison.

This query is for Azure SQL DB i want to join Table 2 with Table 1

where it should satisfy below 2 conditions Table2.Items(each item).product_category = Table1.product_category and Table2.Items(each item).product_id = Table1.product_id

and want to get all items and columns in Table two with expanding each item in JSON items to rows

Table 1

product_category    product_name    product_id  product_cost
Gift                Glass           157         85
Electronics         Bulb            833         218
Kitchen             Glass           157         75

Table 2

Order_ID    Pincode Order_details   Email
HASDUI2N342 766815  <JSON_Data>     [email protected]
ASDIJ234HJI 487957  <JSON_Data>     [email protected]
ASDOI23480H 512878  <JSON_Data>     [email protected]

Sample <JSON_Data> for order_id HASDUI2N342

{
  "order_date": "26-07-2019",
  "Items": [
    {
      "product_category": "Gift",
      "product_id": "157"
    },
    {
      "product_category": "Electronics",
      "product_id": "833"
    }
  ],
  "amount_paid": 333,
  "shipping" :  30

}

Below is the excpect Result Table after joinning both

Order_ID    Pincode Email               Item.product_Name   Item.product_cost
HASDUI2N342 766815  [email protected]    Glass               85
HASDUI2N342 766815  [email protected]    Bulb                218
ASDIJ234HJI 487957  [email protected]    .....               ....
ASDIJ234HJI 487957  [email protected]    .....               ....
ASDOI23480H 512878  [email protected]    .....               ....

What is your question here? You've giving us some of your query, stated your results, but not told us what the end goal is or what problems you're having. — Thom A
– Thom A ♦, Commented Jul 26, 2019 at 12:59
@Larnu I want a SQL query to achieve this, I have updated my question. — Raghavendra C
– Raghavendra C, Commented Jul 26, 2019 at 12:59
So what is your expected results? What have you tried so far? — Thom A
– Thom A ♦, Commented Jul 26, 2019 at 13:07
Last table is the expected result and I tried with open JSON but not working — Raghavendra C
– Raghavendra C, Commented Jul 26, 2019 at 13:15
Please do show us your attempt. We can't show you what was wrong, if we don't have your query. What does "not working" mean? — Thom A
– Thom A ♦, Commented Jul 26, 2019 at 13:19

Zhorov · Accepted Answer · 2019-07-26 13:25:25Z

You need to use OPENJSON() with explicit schema definition and appropriate joins:

Tables:

CREATE TABLE Table1 (
   product_category nvarchar(50),
   product_name nvarchar(50),    
   product_id int,  
   product_cost int
)
INSERT INTO Table1
   (product_category, product_name, product_id, product_cost)
VALUES   
   (N'Gift'        , N'Glass', 157, 85),
   (N'Electronics' , N'Bulb' , 833, 218),
   (N'Kitchen'     , N'Glass', 157, 75)
CREATE TABLE Table2 (
   Order_ID nvarchar(100),
   Pincode int,
   Order_details nvarchar(max),
   Email nvarchar(100)
)
INSERT INTO Table2
   (Order_ID, Pincode, Order_details, Email)
VALUES
   (
   N'HASDUI2N342',
   766815,
   N'{
  "order_date": "26-07-2019",
  "Items": [
    {
      "product_category": "Gift",
      "product_id": "157"
    },
    {
      "product_category": "Electronics",
      "product_id": "833"
    }
  ],
  "amount_paid": 333,
  "shipping" :  30
   }',
   N'[email protected]'
   )

Statement:

SELECT 
   t2.Order_ID, t2.Pincode, t2.Email,
   t1.product_name, t1.product_cost
FROM Table2 t2
CROSS APPLY OPENJSON(t2.Order_details, '$.Items') WITH (
   product_id nvarchar(100) '$.product_id',
   product_category nvarchar(100) '$.product_category'
) j
LEFT JOIN Table1 t1 ON (j.product_id = t1.product_id) AND (j.product_category = t1.product_category)

Output:

Order_ID    Pincode Email               product_name    product_cost
HASDUI2N342 766815  [email protected]    Glass           85
HASDUI2N342 766815  [email protected]    Bulb            218

thank you very much for the effort and help, this query solved my issue.

Collectives™ on Stack Overflow

SQL Server 2016 select where in json array of objects with normal table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related