Parse JSON in TSQL

R0b0tn1k picture R0b0tn1k · May 19, 2010 · Viewed 193.9k times · Source

Is it possible to parse JSON in TSQL?

I don't mean to create a JSON string; rather, I want to parse a JSON string passed in as a parameter.

Answer

Phil Factor picture Phil Factor · Nov 15, 2010

I seem to have a huge masochistic streak in that I've written a JSON parser. It converts a JSON document into a SQL Adjacency list table, which is easy to use to update your data tables. Actually, I've done worse, in that I've done code to do the reverse process, which is to go from a hierarchy table to a JSON string

The article and code is here: Consuming Json strings in SQL server.

Select * from parseJSON('{
  "Person":
  {
     "firstName": "John",
     "lastName": "Smith",
     "age": 25,
     "Address":
     {
        "streetAddress":"21 2nd Street",
        "city":"New York",
        "state":"NY",
        "postalCode":"10021"
     },
     "PhoneNumbers":
     {
        "home":"212 555-1234",
        "fax":"646 555-4567"
     }
  }
}
')

To get:

enter image description here