How to join JSON to table (SQL Server)

Question

I am trying to join an array of JSON to a table in SQL Server (in hopes of avoiding using TVP).

Table Data

| item_id   | qty |
|-----------|-----|
| dur 20001 | 1   |
| dur 93677 | 1   | 

SQL

declare @json nvarchar(max) = '[{
  "order":{
    "address":{
      "addLine": "123 ABC Ln.",
      "citySt": "Los Angeles, CA"'
    },
  "items":[
    "line":{
      "id":"ABC 12345",
      "qty":"1"
  }]}, {
  "order":{
    "address":{
      "addLine": "987 Def Ln.",
      "citySt": "Los Angeles, CA"
    },
  "items":[
    "line":{
      "id":"DEF 12345",
      "qty":"1"
  }]}
]'


select * 
from someTable st
inner join @json 
on vt.item_id in (select json_value(@json,'$.items[0].line.id') 
  from openjson(@json,'$.items[0]'))

However, I'm getting the following error:

Must declare the table variable "@json".

How can I join to someTable with JSON in this form?

Answer 1

You need to consider the fact, that the items part of the input JSON is an array of JSON objects, so it's necessary to use additional CROSS APPLY operator to parse this JSON array:

Table and JSON:

CREATE TABLE #Data (
   idItem varchar(10),
   qty int
)
INSERT INTO #Data 
   (idItem, qty)
VALUES   
   ('DUR 20001', 1),
   ('DUR 93677', 1),
   ('DUR 82674', 1)

DECLARE @json nvarchar(max) = N'[{ 
  "order": { 
    "address":{ 
        "shipToAddLine": null, 
        "shipToCityStZip": "ZipCode"
    }, 
    "items":[ 
        {"line":{ 
            "idItem":"DUR 82674", 
            "qty":"1" 
        }},
        {"line":{ 
            "idItem":"DUR 82674", 
            "qty":"1" 
            }}
    ]
  }}, { 
  "order":{ 
    "address":{ 
        "shipToAddLine": null, 
        "shipToCityStZip": "ZipCode" 
    }, 
    "items":[ 
        {"line":{ 
        "idItem":"DUR 82674", 
        "qty":"1" 
        }}
    ]
  }} 
]'

Statement:

SELECT *
FROM #Data d
JOIN (
   SELECT 
      i.shipToAddLine,
      i.shipToCityStZip,
      j.idItem,
      j.qty
   FROM OPENJSON(@json) WITH (
      shipToAddLine varchar(100) '$.order.address.shipToAddLine',
      shipToCityStZip varchar(100) '$.order.address.shipToCityStZip',
      items nvarchar(max) '$.order.items' AS JSON
   ) i
   CROSS APPLY OPENJSON(i.items) WITH (
      idItem varchar(10) '$.line.idItem',
      qty int '$.line.qty'
   ) j
) json ON d.idItem = json.idItem

Result:

-------------------------------------------------------------------
idItem      qty    shipToAddLine    shipToCityStZip idItem      qty
-------------------------------------------------------------------
DUR 82674   1                       ZipCode         DUR 82674   1
DUR 82674   1                       ZipCode         DUR 82674   1
DUR 82674   1                       ZipCode         DUR 82674   1

Answer 2

if you getting error 'Must declare the table variable "@json"', then you need to run the entire peace of code in one batch. Before you can join json string to the table, you need to parse it. Below is the example of how to do it. I did it on my machine and I'm getting values.

declare @json nvarchar(max) 
set @json = 
'[{ 
"order":{ 
    "address":{ 
        "shipToAddLine":null, 
        "shipToCityStZip":null 
    }, 
    "items":[ 
        {"line":{ 
            "idItem":"DUR 82674", 
            "qty":"1" 
    }}]}}, { 
"order":{ 
    "address":{ 
        "shipToAddLine":null, 
        "shipToCityStZip":null 
    }, 
    "items":[ 
        {"line":{ 
        "idItem":"DUR 82674", 
        "qty":"1" 
}}]}} 
]'



select *
from someTable st join openjson(@json)
with(
itemId varchar(50) '$.order.items[0].line.idItem'
) as js
on st.itemid = js.itemID