Parse JSON in TSQL
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
.
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:
Update: As of SQL Server 2016 parsing JSON in TSQL is now possible.
Natively, there is no support. You'll have to use CLR. It is as simple as that, unless you have a huge masochistic streak and want to write a JSON parser in SQL
Normally, folk ask for JSON output from the DB and there are examples on the internet. But into a DB?
Finally SQL Server 2016 will add Native JSON support!!
Ref:
Additional capabilities in SQL Server 2016 include:
- Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always
Encrypted.- Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load
balancing.- Native JSON support to offer better performance and support for your many types of your data.
- SQL Server Enterprise Information Management (EIM) tools and Analysis Services get an upgrade in performance, usability and scalability.
- Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Azure
and place your SQL Server AlwaysOn secondaries in Azure.
Announcment: http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx
Features blog post: http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx
SQL server 2016 supports json data
parsing using OPENJSON
. You can use OPENJSON
to map json data
to rows and columns.
Your json Data
[
{ "id" : 2,"name": "John"},
{ "id" : 5,"name": "John"}
]
Here is how you can handle json in sql
//@pJson is json data passed from code.
INSERT INTO YourTable (id, Name)
SELECT id, name
FROM OPENJSON(@pJson)
WITH (id int,
name nvarchar(max))
Here is a detailed article which covers this topic.