Hola amigos, bienvenidos a mi blog.
En esta ocasión voy a mostrarles una manera de obtener la información de un string en formato JSON en SQL SERVER.
Quiero mencionar que yo utilizo SQL Server Management Studio 2008 para realizar este post.
Lo primero que deben hacer es copiar y ejecutar la función que a continuación les muestro para crearla.
CREATE FUNCTION parseJSON( @JSON NVARCHAR(MAX)) RETURNS @hierarchy TABLE ( element_id INT IDENTITY(1, 1) NOT NULL, sequenceNo [int] NULL, parent_ID INT, Object_ID INT, NAME NVARCHAR(2000), StringValue NVARCHAR(MAX) NOT NULL, ValueType VARCHAR(10) NOT null ) AS BEGIN DECLARE @FirstObject INT, @OpenDelimiter INT, @NextOpenDelimiter INT, @NextCloseDelimiter INT, @Type NVARCHAR(10), @NextCloseDelimiterChar CHAR(1), @Contents NVARCHAR(MAX), @Start INT, @end INT, @param INT, @EndOfName INT, @token NVARCHAR(200), @value NVARCHAR(MAX), @SequenceNo int, @name NVARCHAR(200), @parent_ID INT, @lenJSON INT, @characters NCHAR(36), @result BIGINT, @index SMALLINT, @Escape INT DECLARE @Strings TABLE ( String_ID INT IDENTITY(1, 1), StringValue NVARCHAR(MAX) ) SELECT @characters='0123456789abcdefghijklmnopqrstuvwxyz', @SequenceNo=0, @parent_ID=0; WHILE 1=1 BEGIN SELECT @start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); IF @start=0 BREAK IF SUBSTRING(@json, @start+1, 1)='"' BEGIN SET @start=@Start+1; SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin); END IF @end=0 BREAK SELECT @token=SUBSTRING(@json, @start+1, @end-1) SELECT @token=REPLACE(@token, FROMString, TOString) FROM (SELECT '\"' AS FromString, '"' AS ToString 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 SELECT @result=0, @escape=1 WHILE @escape>0 BEGIN SELECT @index=0, @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 BEGIN SELECT @result=@result+POWER(16, @index) *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1), @characters)-1), @index=@index+1 ; END SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result)) END END INSERT INTO @Strings (StringValue) SELECT @token SELECT @JSON=STUFF(@json, @start, @end+1, '@string'+CONVERT(NVARCHAR(5), @@identity)) END WHILE 1=1 BEGIN SELECT @parent_ID=@parent_ID+1 SELECT @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' SELECT @OpenDelimiter=@firstObject WHILE 1=1 BEGIN SELECT @lenJSON=LEN(@JSON+'|')-1 SELECT @NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json, @OpenDelimiter+1) SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%', RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin) IF @NextOpenDelimiter=0 BREAK SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter IF @NextCloseDelimiter<@NextOpenDelimiter BREAK IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{' SELECT @NextCloseDelimiterChar='}', @type='object' ELSE SELECT @NextCloseDelimiterChar=']', @type='array' SELECT @OpenDelimiter=@NextOpenDelimiter END SELECT @contents=SUBSTRING(@json, @OpenDelimiter+1, @NextCloseDelimiter-@OpenDelimiter-1) SELECT @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 IF @Type='Object' BEGIN SELECT @SequenceNo=0,@end=CHARINDEX(':', ' '+@contents) SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin) SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1), @endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin), @param=RIGHT(@token, LEN(@token)-@endofname+1) SELECT @token=LEFT(@token, @endofname-1), @Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1) SELECT @name=stringvalue FROM @strings WHERE string_id=@param --fetch the name END ELSE SELECT @Name=null,@SequenceNo=@SequenceNo+1 SELECT @end=CHARINDEX(',', @contents) IF @end=0 SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ' collate SQL_Latin1_General_CP850_Bin) +1 SELECT @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 SELECT @Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)), @Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end) IF SUBSTRING(@value, 1, 7)='@object' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object' ELSE IF SUBSTRING(@value, 1, 6)='@array' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array' ELSE IF SUBSTRING(@value, 1, 7)='@string' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string' FROM @strings WHERE string_id=SUBSTRING(@value, 8, 5) ELSE IF @value IN ('true', 'false') INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean' ELSE IF @value='null' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'null' ELSE IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0 INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'real' ELSE INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'int' if @Contents=' ' Select @SequenceNo=0 END END INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) SELECT '-',1, NULL, '', @parent_id-1, @type RETURN END
Bien, una vez que ya han creado esta función en SQL Server, vamos a hacer una prueba con un string en formato JSON.
Copien y ejecuten el siguiente código en SQL Server
Select * from parseJSON( '{ "success": true, "año": "2014", "mes": "02", "data": [ { "fecha": "2014-02-01", "compra": "2.820", "venta": "2.822" }, { "fecha": "2014-02-04", "compra": "2.823", "venta": "2.825" }, { "fecha": "2014-02-05", "compra": "2.823", "venta": "2.824" }, { "fecha": "2014-02-06", "compra": "2.824", "venta": "2.825" }, { "fecha": "2014-02-07", "compra": "2.821", "venta": "2.822" }, { "fecha": "2014-02-08", "compra": "2.820", "venta": "2.822" }, { "fecha": "2014-02-11", "compra": "2.818", "venta": "2.819" }, { "fecha": "2014-02-12", "compra": "2.816", "venta": "2.817" }, { "fecha": "2014-02-13", "compra": "2.815", "venta": "2.816" }, { "fecha": "2014-02-14", "compra": "2.816", "venta": "2.817" }, { "fecha": "2014-02-15", "compra": "2.812", "venta": "2.815" }, { "fecha": "2014-02-18", "compra": "2.802", "venta": "2.803" }, { "fecha": "2014-02-19", "compra": "2.806", "venta": "2.807" }, { "fecha": "2014-02-20", "compra": "2.807", "venta": "2.809" }, { "fecha": "2014-02-21", "compra": "2.808", "venta": "2.809" }, { "fecha": "2014-02-22", "compra": "2.806", "venta": "2.808" }, { "fecha": "2014-02-25", "compra": "2.805", "venta": "2.808" }, { "fecha": "2014-02-26", "compra": "2.806", "venta": "2.808" }, { "fecha": "2014-02-27", "compra": "2.808", "venta": "2.810" }, { "fecha": "2014-02-28", "compra": "2.802", "venta": "2.804" } ] }' )
El resultado que debes obtener es el siguiente:
Espero que les sea de utilidad, cualquier duda no duden en comentar el post.
Thank you so much.
ResponderEliminar