如何在 SQL Server 2012 中解析 json 数据? [英] How to parse json data in SQL Server 2012?

问题描述

我正在使用 SQL Server 2012.我被分配了一项任务，其中表 Sample 的一列 (JsonText) 包含 json 数据.我想通过解析该数据并插入到另一个表的列中(Test).我在网上搜索 SQL Server 2016 支持openjson".如何在 SQL Server 2012 中执行?

I am using SQL Server 2012.I have been assigned a task where one of my column (JsonText) of table Sample contains json data. I want to pass parse that data and insert into columns of another table (Test). I searched on net 'openjson' is supported in SQL Server 2016. How to do in SQL Server 2012?

表 1:示例

Id JsonText Active 

JsonText

webaddress?{'data':'{"PId": "XXXX","Status": "YES","Name":"XXX","Address":"XXXX","MobileNumber":"xxx"}'}

我只对 'PID,Address,MobileNumber' 列感兴趣，而不是全部.

I am intrested only 'PID,Address,MobileNumber' columns not all.

像这样的表测试结构

Id, PID, Address, MobileNumber

推荐答案

我创建了一个与 SQL 2012 兼容的函数来解决这个问题

I created a function compatible with SQL 2012 to take care of this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Isaac Adams
-- Create date: 7/12/2018
-- Description: Give the JSON string and the name of the column from which you want the value
-- =============================================
CREATE FUNCTION JSON_VALUE
(
    @JSON NVARCHAR(3000),
    @column NVARCHAR(3000)
)
RETURNS NVARCHAR(3000)
AS
BEGIN

DECLARE @value NVARCHAR(3000);
DECLARE @trimmedJSON NVARCHAR(3000);

DECLARE @start INT;
DECLARE @length INT;

SET @start = PATINDEX('%' + @column + '":"%',@JSON) + LEN(@column) + 3;
SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON));
SET @length = PATINDEX('%", "%', @trimmedJSON);
SET @value = SUBSTRING(@trimmedJSON, 0, @length);

RETURN @value
END
GO

这篇关于如何在 SQL Server 2012 中解析 json 数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！