Query the element in Array inside JSON column

Question

I have a jason column in my postgress sql Database. I have several properties in that jason column. I can search properties using below query.

SELECT *  FROM public.object_reference where value->>'name' = 'Sam' and value->>'address' ='home';

But my problem is I have a Array inside that JSON column. That Array has key and value pair. Below is the sample of that array

"attributes": [ {
    "value": "Sam",
    "key": "name"
}, {
    "value": "abc",
    "key": "address"
}, {
    "value": "Singapore",
    "key": "country"
}, {
    "value": "97813245",
    "key": "mobile"
}, {
    "value": "Engineer",
    "key": "position"
},
"id": "1312312",
"type": "Job",
"classid": "1245568956643546788907634"

}

So i need to get the value of name in the attributes array (inside JSON column). This is json type column, not a jsonb type.

Emilio Platzer · Accepted Answer · 2017-12-27 01:48:48Z

1

You can deconstruct the array inside de object transforming it in a set of recordet (pseudo table) with json_array_elements:

select pair->>'value'
  from has_json,json_array_elements(obj->'attributes') as pair
  where pair->>'key' = 'name';

You can see a running example at: http://rextester.com/ONJZ8486

answered Dec 27, 2017 at 1:48

Emilio Platzer

2,5191 gold badge23 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Query the element in Array inside JSON column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related