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 ;-)

score 3 · Accepted Answer · answered May 21 '19 at 06:34

3

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

answered May 21 '19 at 06:34

Zhorov

28,486
6
27
52

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! – GreyOrGray May 21 '19 at 13:52
@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. – Kamil Turowski May 21 '19 at 20:55
@KamilTurowski Glad to help! – Zhorov May 22 '19 at 05:43

Lukasz Szozda · Answer 2 · 2019-05-20T22:08:53.867

2

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

edited May 20 '19 at 22:08

answered May 20 '19 at 21:25

Lukasz Szozda

162,964
23
234
275

2

`rn` should be`ROW_NUMBER() OVER(ORDER BY [key]) AS rn`, as otherwise the return order of the rows is undocumented and subject to change. – David Browne - Microsoft May 20 '19 at 21:52
@DavidBrowne-Microsoft Thanks for hint, now it is fully deterministic :) – Lukasz Szozda May 20 '19 at 21:54
1

@DavidBrowne-Microsoft Actually I don't need row_number at all. – Lukasz Szozda May 20 '19 at 21:56
1

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 – John Cappelletti May 20 '19 at 22:09

Inserting object of two arrays into SQL Database using OPENJSON

2 Answers2