JSON in SQL Server 2016
JSON functions in SQL Server enable you to analyze and query JSON data, transform JSON to relational format, and export SQL query results as JSON text.
If you have JSON text, you can extract data from JSON or verify that JSON is properly formatted using built-in functions JSON_VALUE, JSON_QUERY, and ISJSON. For more advanced querying and analysis, the OPENJSON function can transform an array of JSON objects into a set of rows. Any SQL query can be executed on the returned result set. Finally, there is the FOR JSON clause that enables you to format query results as JSON text.
1 2 3 4 5 6 | SELECT TOP 1000 [Version] ,[OSType] ,[Online] ,[OnlineT] FROM [ApplicationTest].[dbo].[table1] for json auto |
We can start with simple examples. In the following Transact-SQL code, we will define a text variable where we will put JSON text:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DECLARE @json NVARCHAR(4000) SET @json = N '{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' |
Now, we can extract values and objects from JSON text using the JSON_VALUE and JSON_QUERY functions:
1 2 3 4 | SELECT JSON_VALUE(@json, '$.type' ) as type, JSON_VALUE(@json, '$.info.address.town' ) as town, JSON_QUERY(@json, '$.info.tags' ) as tags |
This query will return “Basic”, “Bristol”, and ["Sport", "Water polo"] values. The JSON_VALUE function returns one scalar value from JSON text (e.g. strings, numbers, true/false) that is placed on a JSON path specified as the second parameter. JSON_QUERY returns an object or array (in this example an array of tags) on the JSON path. JSON built-in functions use JavaScript-like syntax to reference values and objects in JSON text via second parameter.
The OPENJSON function enables you to reference some array in JSON text and return elements from that array:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT value FROM OPENJSON(@json, '$.info.tags' ) INSERT INTO Orders(Number, Date , Customer, Quantity) SELECT Number, Date , Customer, Quantity OPENJSON (@orders) WITH ( Number varchar (200), Date datetime, Customer varchar (200), Quantity int ) AS OrdersArray |
Four columns in the result set that is returned by OPENJSON are defined in the WITH clause. OPENJSON will try to find the properties Number, Date, Customer, and Quantity in each JSON object and convert their values into columns in the result set. By default, NULL will be returned if the property is not found. The assumption in the query above is that the @orders variable contains the following JSON array:
1 2 3 4 5 6 | '[ {"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200}, {"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100}, {"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250}, {"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200} ]' |
As you can see, the transformation from a JSON text to a relational form is simple. You just need to specify column names and types and OPENJSON will find properties in JSON that match these columns. In this example, plain JSON is used; however, OPENJSON can handle any nested/hierarchical structure of JSON objects.
Also, OPENJSON can be used to combine relational and JSON data in the same query. If we assume that the JSON array shown in the previous example is stored in the Orders column, the following query can combine the columns and JSON fields:
1 2 3 4 5 6 7 8 | SELECT Id, FirstName, LastName, Number, Date , Customer, Quantity FROM Person CROSS APPLY OPENJSON (OrdersJson) WITH ( Number varchar (200), Date datetime, Customer varchar (200), Quantity int ) AS OrdersArray |
1 2 3 4 5 6 7 8 9 10 | select * from [dbo].[table1] for json auto declare @jsondata varchar ( max ) set @jsondata=N '[{"Version":"2.1.1.1","OSType":1,"Online":1,"OnlineT":"on"},{"Version":"2.1.1.2","OSType":1,"Online":1,"OnlineT":"on"}]' ; select Version from openjson(@jsondata) with ( Version varchar (50) ) |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
· Linux系统下SQL Server数据库镜像配置全流程详解
· Sdcb Chats 技术博客:数据库 ID 选型的曲折之路 - 从 Guid 到自增 ID,再到
· Winform-耗时操作导致界面渲染滞后
· Phi小模型开发教程:C#使用本地模型Phi视觉模型分析图像,实现图片分类、搜索等功能
· 语音处理 开源项目 EchoSharp
· drools 规则引擎和 solon-flow 哪个好?solon-flow 简明教程