CODEWITHSUNDEEP
sqlsql-server
Arthur
Arthur

Reputation: 127

How to iterate over multiple keys using JSON_VALUE

I have JSON stored in the database, that looks like this:

{
    "EmpName": "John Doe",
    "Department": "IT",
    "Address-1": "101, Sector 1, NY",
    "Address-2": "102, Sector 2, SC",
    "Address-3": "103, Sector 3, NY",
    "Address-4": "104, Sector 4, NY",
    "Salary": 100000
}

I am trying to check if the json has the address "102, Sector 2, SC" in it or not using JSON_VALUE.

But there can be multiple addresses for a single JSON object, which are indexed like Address-1/2/3/4

Here is what I am trying to do:

select * 
from emp
where JSON_VALUE(emp.index_data, '$."Address-[*]"') = '102, Sector 2, SC'

I know we cannot have that '[*]' in the key, but is there any way we can achieve this?

Upvotes: 1

Views: 1033

Answers (2)

Charlieface
Charlieface

Reputation: 72020

An option if you don't know the exact column names, is to use OPENJSON without a schema:

SELECT *
FROM emp
WHERE EXISTS (SELECT 1
    FROM OPENJSON(emp.index_data) j
    WHERE j.[key] LIKE 'Address-%' AND
        j.[value] = '102, Sector 2, SC'
)

Upvotes: 1

josh
josh

Reputation: 11

Try this.

SELECT *
FROM emp
CROSS APPLY OPENJSON(emp.index_data)
with(
        varAddressLine1 nvarchar(Max) '$.Address-1',
        varAddressLine2 nvarchar(Max) '$.Address-2',
        varAddressLine3 nvarchar(Max) '$.Address-3',
        varAddressLine4 nvarchar(Max) '$.Address-4'
    )
where varAddressLine1 = '102, Sector 2, SC'
OR varAddressLine2 = '102, Sector 2, SC'
OR varAddressLine3 = '102, Sector 2, SC'
OR varAddressLine4 = '102, Sector 2, SC'

Upvotes: 1

Related Questions