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

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