SQL Server JSON 转行后转列

1.使用ParseJsonFunc JSON转行

USE [DataIntegration]
GO

CREATE FUNCTION [dbo].[ParseJsonFunc]( @json nvarchar(max) )
RETURNS @hierarchy table
(
object_id int NOT NULL, /* [0 -- Not an object] each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
parent_id int NOT NULL, /* [0 -- Root] if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
name nvarchar(2000), /* the name of the object */
stringvalue nvarchar(4000) NOT NULL, /*the string representation of the value of the element. */
valuetype nvarchar(100) NOT NULL, /* the declared type of the value represented as a string in stringvalue*/
bigintvalue bigint,
boolvalue bit
)

BEGIN
DECLARE
@firstobject int, --the index of the first open bracket found in the JSON string
@opendelimiter int, --the index of the next open bracket found in the JSON string
@nextopendelimiter int,--the index of subsequent open bracket found in the JSON string
@nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string
@type nvarchar(10),--whether it denotes an object or an array
@nextclosedelimiterChar CHAR(1),--either a '}' or a ']'
@contents nvarchar(MAX), --the unparsed contents of the bracketed expression
@start int, --index of the start of the token that you are parsing
@end int,--index of the end of the token that you are parsing
@param int,--the parameter at the end of the next Object/Array token
@endofname int,--the index of the start of the parameter at end of Object/Array token
@token nvarchar(4000),--either a string or object
@value nvarchar(MAX), -- the value as a string
@name nvarchar(200), --the name as a string
@parent_id int,--the next parent ID to allocate
@lenjson int,--the current length of the JSON String
@characters NCHAR(62),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@escape int; --the index of the next escape character

/* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped'
* in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in
* the JSON string by tokens representing the string
*/
DECLARE @strings table
(
string_id int IDENTITY(1, 1),
stringvalue nvarchar(MAX)
)

/* initialise the characters to convert hex to ascii */
SET @characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET @parent_id = 0;

/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
WHILE 1 = 1 /* forever until there is nothing more to do */
BEGIN
SET @start = PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); /* next delimited string */

IF @start = 0 BREAK; /*no more so drop through the WHILE loop */

IF SUBSTRING(@json, @start+1, 1) = '"'
BEGIN /* Delimited name */
SET @start = @start+1;
SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);
END

IF @end = 0 /*no end delimiter to last string*/
BREAK; /* no more */

SELECT @token = SUBSTRING(@json, @start+1, @end-1)

/* now put in the escaped control characters */
SELECT @token = REPLACE(@token, from_string, to_string)
FROM
(
SELECT '\"' AS from_string, '"' AS to_string
UNION ALL
SELECT '\\', '\'
UNION ALL
SELECT '\/', '/'
UNION ALL
SELECT '\b', CHAR(08)
UNION ALL
SELECT '\f', CHAR(12)
UNION ALL
SELECT '\n', CHAR(10)
UNION ALL
SELECT '\r', CHAR(13)
UNION ALL
SELECT '\t', CHAR(09)
) substitutions;

SET @result = 0;
SET @escape = 1;

/*Begin to take out any hex escape codes*/
WHILE @escape > 0
BEGIN

/* find the next hex escape sequence */
SET @index = 0;
SET @escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin);

IF @escape > 0 /* if there is one */
BEGIN

WHILE @index < 4 /* there are always four digits to a \x sequence */
BEGIN
/* determine its value */
SET @result = @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @escape + 2 + 3 - @index, 1), @characters) - 1);
SET @index = @index + 1;
END

/* and replace the hex sequence by its unicode value */
SET @token = STUFF(@token, @escape, 6, NCHAR(@result));
END

END

/* now store the string away */
INSERT INTO @strings (stringvalue) SELECT @token;

/* and replace the string with a token */
SET @json = STUFF(@json, @start, @end + 1, '@string' + CONVERT(nvarchar(5), @@identity));

END

/* all strings are now removed. Now we find the first leaf. */
WHILE 1 = 1 /* forever until there is nothing more to do */
BEGIN

SET @parent_id = @parent_id + 1;

/* find the first object or list by looking for the open bracket */
SET @firstobject = PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin); /*object or array*/

IF @firstobject = 0 BREAK;

IF (SUBSTRING(@json, @firstobject, 1) = '{')
SELECT @nextclosedelimiterChar = '}', @type = 'object';
ELSE
SELECT @nextclosedelimiterChar = ']', @type = 'array';

SET @opendelimiter = @firstobject;

WHILE 1 = 1 --find the innermost object or list...
BEGIN
SET @lenjson = LEN(@json+'|') - 1;

/* find the matching close-delimiter proceeding after the open-delimiter */
SET @nextclosedelimiter = CHARINDEX(@nextclosedelimiterChar, @json, @opendelimiter + 1);

/* is there an intervening open-delimiter of either type */
SET @nextopendelimiter = PATINDEX('%[{[[]%',RIGHT(@json, @lenjson-@opendelimiter) collate SQL_Latin1_General_CP850_Bin); /*object*/

IF @nextopendelimiter = 0 BREAK;

SET @nextopendelimiter = @nextopendelimiter + @opendelimiter;

IF @nextclosedelimiter < @nextopendelimiter BREAK;

IF SUBSTRING(@json, @nextopendelimiter, 1) = '{'
SELECT @nextclosedelimiterChar = '}', @type = 'object';
ELSE
SELECT @nextclosedelimiterChar = ']', @type = 'array';

SET @opendelimiter = @nextopendelimiter;
END

/* and parse out the list or name/value pairs */
SET @contents = SUBSTRING(@json, @opendelimiter+1, @nextclosedelimiter-@opendelimiter - 1);

SET @json = STUFF(@json, @opendelimiter, @nextclosedelimiter - @opendelimiter + 1, '@' + @type + CONVERT(nvarchar(5), @parent_id));

WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin)) < > 0
BEGIN /* WHILE PATINDEX */

IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/
BEGIN

SET @end = CHARINDEX(':', ' '+@contents); /*if there is anything, it will be a string-based name.*/
SET @start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); /*AAAAAAAA*/

SET @token = SUBSTRING(' '+@contents, @start + 1, @end - @start - 1);
SET @endofname = PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin);
SET @param = RIGHT(@token, LEN(@token)-@endofname + 1);

SET @token = LEFT(@token, @endofname - 1);
SET @contents = RIGHT(' ' + @contents, LEN(' ' + @contents + '|') - @end - 1);

SELECT @name = stringvalue FROM @strings WHERE string_id = @param; /*fetch the name*/

END
ELSE
BEGIN
SET @name = null;
END

SET @end = CHARINDEX(',', @contents); /*a string-token, object-token, list-token, number,boolean, or null*/

IF @end = 0
SET @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1;

SET @start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin);

/*select @start,@end, LEN(@contents+'|'), @contents */

SET @value = RTRIM(SUBSTRING(@contents, @start, @end-@start));
SET @contents = RIGHT(@contents + ' ', LEN(@contents+'|') - @end);

IF SUBSTRING(@value, 1, 7) = '@object'
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object';

ELSE
IF SUBSTRING(@value, 1, 6) = '@array'
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array';
ELSE
IF SUBSTRING(@value, 1, 7) = '@string'
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, stringvalue, 'string', 0
FROM @strings
WHERE string_id = SUBSTRING(@value, 8, 5);
ELSE
IF @value IN ('true', 'false')
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, boolvalue)
SELECT @name, @parent_id, @value, 'boolean', 0, CASE @value WHEN 'true' THEN 1 ELSE 0 END;
ELSE
IF @value = 'null'
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'null', 0;
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'real', 0;
ELSE
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, bigintvalue)
SELECT @name, @parent_id, @value, 'bigint', 0, CONVERT(BIGINT,@value);

END /* WHILE PATINDEX */

END /* WHILE 1=1 forever until there is nothing more to do */

INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT '', 0, '', @parent_id - 1, @type;

RETURN;

END

2.使用FOR XML PATH 语法转列

--具体实现过程如下

DECLARE @jsonStr VARCHAR(MAX)
SET @jsonStr ='{"header":{"TRL":"384692","PLANT":"TBL_MATERIAL_BARCODE_20220522.TXT","TXT_NAME":"TBL_MATERIAL_BARCODE_20220522.TXT","TXT_NUM":"","TXT_SOURCE":"PRC"},"Data":[{"MACHINE_NO":"MP282HC0","MATERIAL_BARCODE_PRE":"","AUTO_ID":"1","MATERIAL_BARCODE":"3423057715429","CREATE_DATE_TIME":"2022/05/16","MATERIAL_NO":"SDK0T76479","VF_NAME":"","MATERIAL_CLASS_CODE":"WE","PLANT_CODE":"","CS_FILE_TYPE":"","SITE_TYPE":"ODM","MTMSN":"","PACKING_LOT_NO":"MPNXB2516002Z00","MODEL":"82SH0000CD","PRINTED_DESC":"WIN SDK0T76479","PRODUCT_DATE":"2022/05/16","SCAN_DATE":"2022/05/18","LUCKY_NO":"G0HUSUAYLV","SALEORDER":"0030348078","COUNTRY":"CN","QTY":"1","PO":"7520441472","ADD_DEL_FLAG":"A","SHELL_IND":"-"}]}'
IF OBJECT_ID(N'tempdb..#tempJson', N'U') IS NOT NULL
BEGIN
DROP TABLE #tempJson;
END
SELECT name,stringvalue
INTO #tempJson
FROM DataIntegration.dbo.ParseJsonFunc(@jsonStr) WHERE ISNULL(name,'')<> ''

SELECT * FROM #tempJson

SELECT
DISTINCT
TRL = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='TRL' FOR XML PATH('')), 1, 1, ''))
, PLANT = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='PLANT' FOR XML PATH('')), 1, 1, ''))
, TXT_NAME = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='TXT_NAME' FOR XML PATH('')), 1, 1, ''))
, TXT_NUM = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='TXT_NUM' FOR XML PATH('')), 1, 1, ''))
, TXT_SOURCE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='TXT_SOURCE' FOR XML PATH('')), 1, 1, ''))
, MACHINE_NO = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='MACHINE_NO' FOR XML PATH('')), 1, 1, ''))
, MATERIAL_BARCODE_PRE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='MATERIAL_BARCODE_PRE' FOR XML PATH('')), 1, 1, ''))
, AUTO_ID = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='AUTO_ID' FOR XML PATH('')), 1, 1, ''))
, MATERIAL_BARCODE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='MATERIAL_BARCODE' FOR XML PATH('')), 1, 1, ''))
, CREATE_DATE_TIME = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='CREATE_DATE_TIME' FOR XML PATH('')), 1, 1, ''))
, MATERIAL_NO = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='MATERIAL_NO' FOR XML PATH('')), 1, 1, ''))
, VF_NAME = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='VF_NAME' FOR XML PATH('')), 1, 1, ''))
, MATERIAL_CLASS_CODE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='MATERIAL_CLASS_CODE' FOR XML PATH('')), 1, 1, ''))
, PLANT_CODE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='PLANT_CODE' FOR XML PATH('')), 1, 1, ''))
, CS_FILE_TYPE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='CS_FILE_TYPE' FOR XML PATH('')), 1, 1, ''))
, SITE_TYPE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='SITE_TYPE' FOR XML PATH('')), 1, 1, ''))
, MTMSN = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='MTMSN' FOR XML PATH('')), 1, 1, ''))
, PACKING_LOT_NO = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='PACKING_LOT_NO' FOR XML PATH('')), 1, 1, ''))
, MODEL = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='MODEL' FOR XML PATH('')), 1, 1, ''))
, PRINTED_DESC = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='PRINTED_DESC' FOR XML PATH('')), 1, 1, ''))
, PRODUCT_DATE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='PRODUCT_DATE' FOR XML PATH('')), 1, 1, ''))
, SCAN_DATE = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='SCAN_DATE' FOR XML PATH('')), 1, 1, ''))
, LUCKY_NO = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='LUCKY_NO' FOR XML PATH('')), 1, 1, ''))
, SALEORDER = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='SALEORDER' FOR XML PATH('')), 1, 1, ''))
, COUNTRY = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='COUNTRY' FOR XML PATH('')), 1, 1, ''))
, QTY = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='QTY' FOR XML PATH('')), 1, 1, ''))
, PO = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='PO' FOR XML PATH('')), 1, 1, ''))
, ADD_DEL_FLAG = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='ADD_DEL_FLAG' FOR XML PATH('')), 1, 1, ''))
, SHELL_IND = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='SHELL_IND' FOR XML PATH('')), 1, 1, ''))
, header = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='header' FOR XML PATH('')), 1, 1, ''))
, Data = (STUFF((SELECT ',' + stringvalue FROM #tempJson WHERE name='Data' FOR XML PATH('')), 1, 1, ''))
FROM #tempJson AS T

发表于 2022-12-27 16:52 Abel, 阅读(243) 评论(0) 编辑收藏举报

SQL Server JSON 转行 后转列