I have a Json string. I Want to get value from that Json string.
This is my json string
{"latitude":"22.5712854"},{"longitude":"88.4266847"}
I want only latitude and longitude from this, using TSQL query.
2 Answers
There is no native way to parse JSON in TSQL. But Phil Factor created his own implementation of paring JSON in SQL function. More about it on simple talk blog in article: Consuming JSON Strings in SQL Server
Aslo Ric Vander Ark created his own function but I did not tested it. You can read more on article: A Function to Split JSON Data
2 Comments
rainabba
"There is no native way to parse JSON in TSQL. But Phil Factor created his own implementation [to parse JSON in TSQL]" <-- Contradiction at its best. Still, thanks for the link on how to parse JSON in TSQL. :)
Runonthespot
A custom implementation, as listed is different to a native (i.e. built in) way.
You could use JSON Select which has several functions for extracting different datatypes from JSON. For your requirements you could do something like this:
select
dbo.JsonDecimal(my_column, 'latitude') as latitude,
dbo.JsonDecimal(my_column, 'longitude') as longitude
from my_table
DISCLOSURE: I am the author of JSON Select, and as such have an interest in you using it :)
1 Comment
Manish Pansiniya
Hi @joshuahealy, Could you please provide documentation of JsonSelect as the site is not online right now.
SUBSTRINGandCHARINDEX). It will almost certainly be easier to do this using code outside the database or possibly a CLR procedure; that way you can use a language that has JSON support or at least good text processing functions.JSONsupport in SQL Server 2016.