Transform JSON: select one row from array of json objects

Question

I can't get a specific row from this JSON array. So I want to get the object where filed 'type' is equal to 'No-Data'

Are there exist any functions in SQL to take the row or some expressions?

 "metadata": { "value": "JABC" },
 "force": false
 "users": [
     { "id": "111", "comment": "aaa", type: "Data" },
     { "id": "222", "comment": "bbb" , type:"No-Data"},
     { "id": "333", "comment": "ccc", type:"Data" }
 ]

You json demo is not correct formed. see demo

jian
– jian

2022-08-11 05:40:55 +00:00
Commented Aug 11, 2022 at 5:40 — jian
– jian, Commented Aug 11, 2022 at 5:40

score 1 · Accepted Answer · 2022-08-11 06:06:05Z

1

You can use a JSON path query:

select jsonb_path_query_first(the_column, '$.users[*] ? (@.type == "No-Data")')
from the_table

This assumes that the column is defined as jsonb (which it should be). If it's not you have to cast it: the_column::jsonb

Online example

edited Aug 11, 2022 at 6:06

answered Aug 11, 2022 at 5:27

user330315

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

3 Comments

MrGa Over a year ago

@a-horse-with-no-name also, can I extract only id from this json?

user330315 Over a year ago

Just use ->> 'id' on the result. But your question was "to get the object" which is what I answered.

MrGa Over a year ago

yeah, thank you a lot, I have figured out only now

Collectives™ on Stack Overflow

Transform JSON: select one row from array of json objects

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related