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

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, 2019 at 6:34

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GreyOrGray Over a year ago

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!

Kamil Turowski Over a year ago

@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.

Zhorov Over a year ago

@KamilTurowski Glad to help!

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

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, 2019 at 22:08

answered May 20, 2019 at 21:25

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

4 Comments

David Browne - Microsoft Over a year ago

rn should beROW_NUMBER() OVER(ORDER BY [key]) AS rn, as otherwise the return order of the rows is undocumented and subject to change.

Lukasz Szozda Over a year ago

@DavidBrowne-Microsoft Thanks for hint, now it is fully deterministic :)

Lukasz Szozda Over a year ago

@DavidBrowne-Microsoft Actually I don't need row_number at all.

John Cappelletti Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related