Is there a way to parameterize the second argument of openjson

Question

My json data looks like:

SET @json='{
    "_id": "4erutit8979044kd5",
    "ADDRESSES": {
        "1": {
            "VALIDATED": "",
            "TYPE": "billing",
            "RESIDENTIAL": "",
            "REGION": "ON",
            "POSTAL": "L6789W",
            "PO": 0,
            "PHONE": "222222",
            "NAME_2": "Kapil",
            "NAME_1": "Kaushal",
            "LINE_2": "",
            "LINE_1": "215 Wards Ct.",
            "EMAIL": "kapilk@gmail.com",
            "COUNTRY": "IN",
            "CITY": "Jodhpur",
            "LAST_USED": 1435590000.0
        },
        "2": {
            "TYPE": "billing",
            "RESIDENTIAL": "",
            "REGION": "JD",
            "POSTAL": "2222",
            "PO": 0,
            "PHONE": "",
            "NAME_2": "Salman",
            "NAME_1": "Kursheed",
            "LINE_2": "",
            "LINE_1": "1459 Thomas Street",
            "EMAIL": "salmank@gmail.com",
            "COUNTRY": "IN",
            "CITY": "Jodhpur",
            "LAST_USED": 1436460000.0,
            "VALIDATED": "dirty"
        },
        "3": {
            "VALIDATED": "clean",
            "TYPE": "shipping",
            "CITY": "Jaisalmer",
            "COUNTRY": "IN",
            "EMAIL": "rajk@gmail.com",
            "LINE_1": "1020 Carripa Enclave",
            "LINE_2": "",
            "NAME_1": "Raj",
            "NAME_2": "Kumar",
            "PO": 0,
            "POSTAL": "222234",
            "REGION": "JS",
            "LAST_VALIDATED": "2015-07-14T16:20:42.242Z",
            "LAST_USED": 1436460000.0
        }
    }, ...
and so on

I want to achieve something like:

WHILE @cnt < 3
BEGIN
   --insert into dummy_table
   select *   
   from   openjson (@json,'$.ADDRESSES."@cnt"')
   SET @cnt = @cnt + 1;
END;

The line is working fine as:

select *   from   openjson (@json,'$.ADDRESSES."1"')
select *   from   openjson (@json,'$.ADDRESSES."1"')

But not with a variable @cnt, even if i define @cnt as char = '1'.

Please help me achieve the same. The no of addresses may differ, so i want to make it dynamic.

What is your SQL Server version? – Zhorov Apr 21 '20 at 10:46 — Zhorov, Apr 21 '20 at 10:46

score 1 · Answer 1 · answered Apr 21 '20 at 09:20

Try this:

DECLARE @json NVARCHAR(MAX);

SET @json='{
    "_id": "4erutit8979044kd5",
    "ADDRESSES": {
        "1": {
            "VALIDATED": "",
            "TYPE": "billing",
            "RESIDENTIAL": "",
            "REGION": "ON",
            "POSTAL": "L6789W",
            "PO": 0,
            "PHONE": "222222",
            "NAME_2": "Kapil",
            "NAME_1": "Kaushal",
            "LINE_2": "",
            "LINE_1": "215 Wards Ct.",
            "EMAIL": "kapilk@gmail.com",
            "COUNTRY": "IN",
            "CITY": "Jodhpur",
            "LAST_USED": 1435590000.0
        },
        "2": {
            "TYPE": "billing",
            "RESIDENTIAL": "",
            "REGION": "JD",
            "POSTAL": "2222",
            "PO": 0,
            "PHONE": "",
            "NAME_2": "Salman",
            "NAME_1": "Kursheed",
            "LINE_2": "",
            "LINE_1": "1459 Thomas Street",
            "EMAIL": "salmank@gmail.com",
            "COUNTRY": "IN",
            "CITY": "Jodhpur",
            "LAST_USED": 1436460000.0,
            "VALIDATED": "dirty"
        },
        "3": {
            "VALIDATED": "clean",
            "TYPE": "shipping",
            "CITY": "Jaisalmer",
            "COUNTRY": "IN",
            "EMAIL": "rajk@gmail.com",
            "LINE_1": "1020 Carripa Enclave",
            "LINE_2": "",
            "NAME_1": "Raj",
            "NAME_2": "Kumar",
            "PO": 0,
            "POSTAL": "222234",
            "REGION": "JS",
            "LAST_VALIDATED": "2015-07-14T16:20:42.242Z",
            "LAST_USED": 1436460000.0
        }
    }'


DECLARE @cnt INT = 1;
DECLARE @query NVARCHAR(128);

WHILE @cnt <= 3
BEGIN
--insert into dummy_table

SET @query = '$.ADDRESSES."' + CAST(@cnt AS VARCHAR(4)) + '"'

select *   
from   openjson (@json,@query)
SET @cnt = @cnt + 1;
END;

Just build the second parameter as a string and pass it.

No it's not working.. It says incorrect syntax near @query in the line openjson(@json,@query). — Paras Singh, Apr 21 '20 at 10:00
@ParasSingh Do you copy the whole template? I am executing this on machine and it is working fine? — gotqn, Apr 21 '20 at 10:48
Thanks for the answer, i may be doing something wrong, but i got the results using the option 1 from the above answer by Zhorov. I will try to figure out the second option as well later. Thanks a lot! — Paras Singh, Apr 21 '20 at 16:23

Zhorov · Accepted Answer · 2020-04-21T19:21:32.187

The exact answer is Yes, but it depends on the SQL Server version. You have the follolwing options:

For SQL Server 2017+, you can provide a variable as the value of path. Note that, when the key name starts with a dollar sign or contains special characters such as spaces or numbers, you need to surround it with quotes.

DECLARE @cnt int
SET @cnt = 1

--INSERT INTO dummy_table
SELECT *
FROM OPENJSON(@json, CONCAT('$.ADDRESSES."', @cnt, '"'))

For SQL Server 2016+ you need to parse the input JSON with OPENJSON() and a default schema. The result is a table with columns key, value and type.

DECLARE @cnt int
SET @cnt = 1

--INSERT INTO dummy_table
SELECT j2.*
FROM OPENJSON(@json, '$.ADDRESSES') j1
CROSS APPLY OPENJSON(j1.[value]) j2
WHERE j1.[key] = @cnt

Result:

key        value         type
VALIDATED                   1
TYPE       billing          1
RESIDENTIAL                 1
REGION     ON               1
POSTAL     L6789W           1
PO         0                2
PHONE      222222           1
NAME_2     Kapil            1
NAME_1     Kaushal          1
LINE_2                      1
LINE_1     215 Wards Ct.    1
EMAIL      kapilk@gmail.com 1
COUNTRY    IN               1
CITY       Jodhpur          1
LAST_USED  1435590000.0     2

As an additional option, you don't need a WHILE loop to get specific values from the input JSON. The following statement parses the values from $.ADDRESSES.1, $.ADDRESSES.2 and $.ADDRESSES.3 keys.

--INSERT INTO dummy_table
SELECT j2.*
FROM (VALUES (1), (2), (3)) v(cnt)
JOIN OPENJSON(@json, '$.ADDRESSES') j1 ON CONVERT(int, j1.[key]) = v.cnt
CROSS APPLY OPENJSON(j1.[value]) j2

Thanks for the answer @Zhorov.. Although i am using SSMS 2016+, but the second method is not working.. Although i got the result using the first option, thanks a lot. — Paras Singh, Apr 21 '20 at 16:22
@ParasSingh, SSMS is perhaps SQL Server Management Studio, which is a client tool. Execute `SELECT @@VERSION` to get the actual version of SQL Server. Using a variable as path is available from SQL Server 2017+ (version 14). — Zhorov, Apr 21 '20 at 16:35

Is there a way to parameterize the second argument of openjson

2 Answers2