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?

Are you sure that you have the correctly formatted json string? I'm trying to parse it on my machine and it just throws an error — Kelevra, Sep 30 '19 at 18:28
Hm... possibly. `[{ "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" }}]}} ]` — Matthew, Sep 30 '19 at 18:31
@Matthew I added an additional `sql-server-json` tag to the question. If you think, that this is not appropriate, please remove the tag. Thanks. — Zhorov, Oct 01 '19 at 07:22

score 2 · Answer 1 · answered Sep 30 '19 at 20:05

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

Kelevra · Accepted Answer · 2019-09-30T18:56:57.860

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

edited Sep 30 '19 at 18:56

answered Sep 30 '19 at 18:07

Kelevra

116
8

Your first comment is noted, and has been corrective. – Matthew Sep 30 '19 at 18:13
However, I'm getting `Incorrect syntax near the keyword 'from'.` with the second example, and also `Incorrect syntax near the keyword 'with'` – Matthew Sep 30 '19 at 18:13
@Matthew I'm sorry I had a typo. try to run it again. I edited the second 'from' and put 'join' instead. – Kelevra Sep 30 '19 at 18:17
Hm. It looks good, but is not returning any records. When I select directly from `someTable` with each `itemId` I get two records... – Matthew Sep 30 '19 at 18:19
Can you provide some sample data from 'someTable' that will be related to json string – Kelevra Sep 30 '19 at 18:21
Yes, just added some. – Matthew Sep 30 '19 at 18:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/200192/discussion-between-kelevra-and-matthew). – Kelevra Sep 30 '19 at 18:31
BOOM. GOLDEN. Thank you @kelevra!! – Matthew Sep 30 '19 at 18:57

How to join JSON to table (SQL Server)

2 Answers2

Linked