SQL Server中的JSON数据处理

I have a Json string. I Want to get value from that Json string.

我有一个Json字符串。我想从那个Json字符串中获取价值。

This is my json string {"latitude":"22.5712854"},{"longitude":"88.4266847"}

这是我的json字符串{“latitude”:“22.5712854”},{“经度”:“88.4266847”}

I want only latitude and longitude from this, using TSQL query.

我想要使用TSQL查询,只需要纬度和经度。

2 个解决方案

#1

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

在TSQL中没有解析JSON的本机方法。但Phil Factor在SQL函数中创建了自己的配对JSON的实现。有关它的更多信息,请参阅文章中的简单谈话博客:在SQL Server中使用JSON字符串

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

Aslo Ric Vander Ark创造了他自己的功能,但我没有测试它。您可以阅读有关文章的更多信息:分割JSON数据的函数

#2

You could use JSON Select which has several functions for extracting different datatypes from JSON. For your requirements you could do something like this:

您可以使用JSON Select,它具有多个函数,用于从JSON中提取不同的数据类型。根据您的要求,您可以这样做:

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 :)

披露:我是JSON Select的作者,因此您对使用它感兴趣:)

#1