parsing JSON column in SQL Server

Question

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?

See that green checkmark in the answer below? It's calling for you ;) — WestCoastProjects
– WestCoastProjects, Commented Feb 29, 2024 at 16:46

Paul Maxwell · Accepted Answer · 2017-11-09 23:35:02Z

9

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

edited Nov 9, 2017 at 23:35

answered Nov 9, 2017 at 22:59

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

4 Comments

CarCrazyBen Over a year ago

Clearly, I am still doing something wrong. Applying ISJSON to each record returns 1 for all non-null entries. So far, so good. However, JSON_QUERY still returns all NULLs: select distinct PAYLOAD_LINE_SEG, JSON_QUERY(PAYLOAD_LINE_SEG,'$.pipeDiameter') as pipe_info from SYSTEM_BLOWDOWNS returns: [{"lineName":"AQ46-5","pipeDiameter":"10","pipeLength":"15620","pressure":"75","duration":"no","volume":"158"}] NULL What am I missing?

CarCrazyBen Over a year ago

No, my question is not completely resolved. I tried to provide a sample from my JSON Table column, along with my JSON_QUERY syntax in my previous comment. I still cannot seem to get JSON_QUERY to work and I don't understand why not. Thank you!

Himilou Over a year ago

I upvoted your answer. This was exactly what I was trying to figure out, thanks for the fiddle!

Paul Maxwell Over a year ago

(re comment back in Nov 13 2017) Use OPENJSON in the from clause and NOT the select clause (as the documentation snippet seen above states) plus if there is more than one "row" in the JSON to be returned then also use cross apply or outer apply to permit those multiple rows to be returned. see this updated dbfiddle.uk/…

Collectives™ on Stack Overflow

parsing JSON column in SQL Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related