How to iterate over multiple keys using JSON_VALUE

Question

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?

look up the documentation for openjson

George Menoutis
– George Menoutis

2021-04-29 07:31:48 +00:00
Commented Apr 29, 2021 at 7:31 — George Menoutis
– George Menoutis, Commented Apr 29, 2021 at 7:31

Charlieface · Accepted Answer · 2021-04-29 09:25:14Z

1

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

answered Apr 29, 2021 at 9:25

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

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

Comments

josh · Accepted Answer · 2021-04-29 07:49:13Z

1

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'

answered Apr 29, 2021 at 7:49

josh

111 bronze badge

3 Comments

Maximilian Ast Over a year ago

Why should the OP "try this"? It's normaly considered good form to explain your suggestions/answers. Code only answers can be surprisingly uninformative, even if they are technically correct. A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer.

Arthur Over a year ago

The number of addresses can vary, that is the tricky part here. But your solution is good for someone looking over fixed number of keys.

Charlieface Over a year ago

For your version where '102, Sector 2, SC' IN (varAddressLine1, varAddressLine2, varAddressLine3, varAddressLine4) is a bit shorter

Collectives™ on Stack Overflow

How to iterate over multiple keys using JSON_VALUE

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related