Search SQL JSON array of objects

Question

I have a SQL column that contains JSON such as this

{
"tags":[
{"id":11,"x":99.12343123213,"y":88.123232},
{"id":12,"x":99.12343123213,"y":88.123232},
{"id":13,"x":99.12343123213,"y":88.123232}
]
}

I am trying to find a record with the value of '13', however the number of objects may vary. I know that i could use code similar to:

select * from Logs WHERE JSON_VALUE([log],'$.tags[0].id') LIKE '13'

Unfortunately, the number of objects in the array could be any number. How could I search through every object in my array for the value?

I am aware I could search through the string for the value of '%"id":13,%', however this seems like a messy solution.

What variety of SQL are you using?

Nick
– Nick

2019-02-17 01:14:34 +00:00
Commented Feb 17, 2019 at 1:14 — Nick
– Nick, Commented Feb 17, 2019 at 1:14
SQL Server 2017

Christopher Vickers
– Christopher Vickers

2019-02-17 01:16:25 +00:00
Commented Feb 17, 2019 at 1:16 — Christopher Vickers
– Christopher Vickers, Commented Feb 17, 2019 at 1:16

Nick · Accepted Answer · 2019-02-17 01:52:17Z

10

You can use OPENJSON to extract the id values from your JSON objects and CROSS APPLY that to your logs table, selecting only rows that have an id value in the JSON object of 13:

SELECT logs.*
FROM logs
CROSS APPLY OPENJSON([log], '$.tags') WITH (id INT '$.id')
WHERE id = 13

Demo on dbfiddle

answered Feb 17, 2019 at 1:52

Nick

147k23 gold badges67 silver badges106 bronze badges

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

2 Comments

Wisnu Wijokangko Over a year ago

how can I use this for mariadb?

Nick Over a year ago

This is SQL server specific. I suggest asking a new question as the solution would be quite different for MariaDB

Collectives™ on Stack Overflow

Search SQL JSON array of objects

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related