可以使用SQL从JSON数组中选择值吗?

I have a database in SQL Server 2008 and one particular table has information stored in fields as JSON-encoded arrays. I'm wondering there's a SQL-based approach to select out specific values within that JSON field?

我在SQL Server 2008中有一个数据库，一个特定的表以json编码的数组的形式存储在字段中。我想知道是否有一种基于sql的方法可以在JSON字段中选择特定的值?

I can, of course, just select the field and parse the information out myself, but I'm trying to avoid that if at all possible.

当然，我可以自己选择字段并解析信息，但如果可能的话，我尽量避免这样做。

Thanks in advance!

提前谢谢!

2 个解决方案

#1

There's nothing natively, but the first answer on the follow question references an article about parsing JSON objects in tSQL

这里没有什么是原生的，但是以下问题的第一个答案引用了一篇关于在tSQL中解析JSON对象的文章

Parse JSON in TSQL

解析JSON在TSQL

For reference, the article of interest is here:

供参考的是:

http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

#2

No (well, there's substring matching, but that would be slow and error-prone). If you're storing something you want to filter with SQL, do not use JSON, use separate columns/tables instead.

没有(嗯，有子字符串匹配，但那将会很慢而且容易出错)。如果您想用SQL来存储一些东西，不要使用JSON，而是使用单独的列/表。

#1