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.

Zhorov · Accepted Answer · 2020-09-17 08:20:49Z

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

edited Sep 17, 2020 at 8:20

answered Sep 17, 2020 at 8:10

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Laziale Over a year ago

Thanks @Zhorov. How can I select multiple fields, for example, we will add two more values eventually in that json object, UnitRate and Total, how will I be able to select all three values? I'll update my question now and I'll also select your answer as correct.

Collectives™ on Stack Overflow

Query SQL column with json values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related