Query SQL column with json values

Question

I have this JSON value stored in SQL text column:

[
  {
    "Id": 1807080,
    "InvoiceId": 1605555,
    "UnitRate": 6924.00,
    "Total": 6924.00
  },
  {
    "Id": 1807081,
    "InvoiceId": 1605555,
    "UnitRate": 16924.00,
    "Total": 16924.00
  },
  {
    "Id": 1807082,
    "InvoiceId": 1605555
  }
]

I'm trying to select all the id values, trying something like this:

select JSON_VALUE(Items, '$.Id') as IdValue 
from InvoiceTable 

I'm querying JSON values for first time and I guess I'm not doing it right, the output I'm getting is null.

Answer 1

You need to use OPENJSON() and explicit schema (columns definitions) and an APPLY operator to parse the JSON content.

Table:

CREATE TABLE InvoiceTable (Items varchar(1000))
INSERT INTO InvoiceTable (Items) 
VALUES ('
    [
      {
        "Id": 1807080,
        "InvoiceId": 1605555,
        "UnitRate": 6924.00,
        "Total": 6924.00
      },
      {
        "Id": 1807081,
        "InvoiceId": 1605555,
        "UnitRate": 16924.00,
        "Total": 16924.00
      },
      {
        "Id": 1807082,
        "InvoiceId": 1605555
      }
    ]
')

Statement:

SELECT j.*
FROM InvoiceTable i
CROSS APPLY OPENJSON(i.Items) WITH (
   Id int '$.Id',
   InvoiceId int '$.InvoiceId',
   UnitRate numeric(10, 2) '$.UnitRate',
   Total numeric(10, 2) '$.Total'
) j

Result:

Id      InvoiceId   UnitRate      Total
1807080 1605555      6924.00    6924.00
1807081 1605555     16924.00   16924.00
1807082 1605555