Inserting object of two arrays into SQL Database using OPENJSON

Question

I wonder how can I put this JSON object, consisting of two float numbers arrays, into SQL Database Table:

{ "Latitudes": [ 53.665084, 53.665084, 53.665184, 53.665284 ], "Longitudes":[ 17.342853, 17.342953, 17.343053, 17.343153 ] }

Table has 3 columns: CoordID, Latitude, Longitude (CoordID is auto-incremented). I would like to put each pair of those tables ( [0],[0] / [1][1] etc. ) as one row in table. So with this object, it would be 4 rows.

Here is the code which doesn't work as I expect - it just puts nulls in place of "Longitude" and "Latitude" columns and it only creates ONE ROW... (Instead of 4 rows):

declare @json NVARCHAR(MAX); SET @json= '{ "Latitudes": [ 53.665084, 53.665084, 53.665184, 53.665284 ], "Longitudes":[ 17.342853, 17.342953, 17.343053, 17.343153 ] }' insert into dbo.Coords( [Latitude],[Longitude]) select Latitude, Longitude from OPENJSON(@json) WITH( Latitude FLOAT '$."Latitudes"', Longitude FLOAT '$."Longitudes"')

P.S OPENJSON() function works from version 2016 of SQL Server or on the Azure SQL Database. Just in case if anyone would try to run this in SSMS 2014 i.e ;-)

Zhorov · Accepted Answer · 2019-05-21 06:34:51Z

You can retrieve your data using only OPENJSON(). The important part here is the fact, that when OPENJSON() parses a JSON array, the function returns the indexes of the elements in this array as keys, so you can join result on these indexes.

T-SQL:

DECLARE @json NVARCHAR(MAX); SET @json= '{ "Latitudes": [ 53.665084, 53.665084, 53.665184, 53.665284 ], "Longitudes": [ 17.342853, 17.342953, 17.343053, 17.343153 ] }' --INSERT INTO dbo.Coords([Latitude], [Longitude]) SELECT j1.[value], j2.[value] FROM OPENJSON(@json, '$.Latitudes') j1, OPENJSON(@json, '$.Longitudes') j2 WHERE j1.[key] = j2.[key]

Output (from SELECT statement):

--------------------- value value --------------------- 53.665084 17.342853 53.665084 17.342953 53.665184 17.343053 53.665284 17.343153

I knew there was a way to do this when I was looking at it yesterday but, for the life of me, I couldn't get it sorted. Sticking this in my notes. Thanks for this!
@Zhorov WORKING LIKE A CHARM. Exactly what I needed - huge Thank You! I was even able to combine it with object consisting of 3 addition key:value pairs.

Lukasz Szozda · Accepted Answer · 2019-05-20 22:08:53Z

Using JSON_QUERY to parse array:

declare @json NVARCHAR(MAX) = '{ "Latitudes": [ 53.665084, 53.665084, 53.665184, 53.665284 ], "Longitudes":[ 17.342853, 17.342953, 17.343053, 17.343153 ] }'; WITH cte1 AS ( select A.value Latitude, [key] AS rn from OPENJSON(JSON_QUERY(@json,'$.Latitudes')) A ), cte2 AS ( select A.value Longitude,[key] AS rn from OPENJSON(JSON_QUERY(@json,'$.Longitudes')) A ) --insert into dbo.Coords([Latitude],[Longitude]) SELECT Latitude, Longitude FROM cte1 JOIN cte2 ON cte1.rn = cte2.rn;

db<>fiddle demo

rn should beROW_NUMBER() OVER(ORDER BY [key]) AS rn, as otherwise the return order of the rows is undocumented and subject to change.
@DavidBrowne-Microsoft Thanks for hint, now it is fully deterministic :)
@DavidBrowne-Microsoft Actually I don't need row_number at all.
My first thought was a CROSS APPLY, but on a side-by-side comparison, yours was only 7% of the batch while mine was 93%... +1

Collectives™ on Stack Overflow

Inserting object of two arrays into SQL Database using OPENJSON

2 Answers 2

3 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Related