CODEWITHSUNDEEP
jsonsql-server
CarCrazyBen
CarCrazyBen

Reputation: 1136

parsing JSON column in SQL Server

I have a JSON string stored in a SQL Server table column. The column is named MSSG_RECIPS and contains one long text string like:

`{"type":"email","recipient":"\"Artzer, Daniel J\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Friedman, Brian\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Higgins, Laura L\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Landenberger, Dan R\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Leitl, Scott\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Mendoza, Mario\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"}`

This sample string illustrates the format of my JSON, each element being comma-separated.

I am able to parse this 2 ways. One, using JSON_VALUE, I can retrieve the attributes as individual columns:

select
 JSON_VALUE(mssg_recips, '$.type'),
 JSON_VALUE(mssg_recips, '$.recipient'),
 JSON_VALUE(mssg_recips, '$.sentTS'),
 DOC_ID
from MY_JSON_TAB

However, this only returns the 1st element of the JSON.

The other method I tried is:

select doc_id, value as EMAIL_RECIP
from MY_JSON_TAB
Cross Apply OPENJSON(MSSG_RECIPS)

This returned the record as rows instead of columns but, again, only the 1st element.

How do I traverse downward, so to speak, to retrieve the 2nd, 3rd, and so on elements?

Upvotes: 13

Views: 33458

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35623

JSON_VALUE returns a single scalar value. For an array of a nested JSON objects you can use JSON_QUERY.

When using CROSS APPLY OPENJSON() WITH( ...) you need to specify the properties of the JSON array you want to draw out e.g.

select id, recipient, sentTS
from my_json_tab
CROSS APPLY OPENJSON(my_json) WITH(
       recipient varchar(200)
     , sentTS varchar(60)
     ) as my_json_array

+----+-------------------------------------------+--------------------------+
| id |                 recipient                 |          sentTS          |
+----+-------------------------------------------+--------------------------+
|  1 | "AAAA, Daniel J" <[email protected]> | 2017-11-08T20:58:14.600Z |
|  1 | "BBBB, Brian" <[email protected]>   | 2017-11-08T20:58:14.600Z |
|  1 | "CCCC, Laura L" <[email protected]> | 2017-11-08T20:58:14.600Z |
|  2 | "xxxx, Daniel J" <[email protected]> | 2017-11-08T20:58:14.600Z |
+----+-------------------------------------------+--------------------------+

NB: When storing multi-element JSON you need to contain the entire JSON within [ ]

In the example result ID=1 has [ json-here ] but id=2 does not, notice the difference in outcome. All element of ID=1 are returned, but that is not true for id=2.

See this dbfiddle for a live demo of the example above.

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns. Since OPENJSON returns a set of rows, you can use OPENJSON in the FROM clause of a Transact-SQL statement just as you can use any other table, view, or table-valued function. https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

Upvotes: 8

Related Questions