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>     user1@domain.com
ASDIJ234HJI 487957  <JSON_Data>     user2@domain.com
ASDOI23480H 512878  <JSON_Data>     user2@domain.com

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  user1@domain.com    Glass               85
HASDUI2N342 766815  user1@domain.com    Bulb                218
ASDIJ234HJI 487957  user2@domain.com    .....               ....
ASDIJ234HJI 487957  user2@domain.com    .....               ....
ASDOI23480H 512878  user3@domain.com    .....               ....

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, Jul 26 '19 at 12:59
@Larnu I want a SQL query to achieve this, I have updated my question. — Raghavendra C, Jul 26 '19 at 12:59
So what is your expected results? What have you tried so far? — Thom A, Jul 26 '19 at 13:07
Last table is the expected result and I tried with open JSON but not working — Raghavendra C, Jul 26 '19 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, Jul 26 '19 at 13:19

score 1 · Accepted Answer · answered Jul 26 '19 at 13:25

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'user1@domain.com'
   )

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  user1@domain.com    Glass           85
HASDUI2N342 766815  user1@domain.com    Bulb            218

thank you very much for the effort and help, this query solved my issue. — Raghavendra C, Jul 26 '19 at 13:38

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

1 Answers1