How to get each JSON child of an array, postgreSQL

Question

I have postgreSQL 9.3 and working with json, my field json in DB looks like:

{
    "route_json": [
        {
            "someKeys": "someValues",
            "time": 123
        },
        {
            "someKeys": "someValues",
            "time": 123
        }, ... N
    ]
}

In my case I need to catch the 'time' element from each element of route_json array and set them in new array. Is there any way to do this.

Nate · Accepted Answer · 2013-12-30 16:06:46Z

3

It’s not pretty:

SELECT
  value->'time'
FROM 
  json_array_elements('{"route_json": [{"someKeys": "someValues","time": 123},{"someKeys": "someValues","time": 456}]}'::json->'route_json');

answered Dec 30, 2013 at 16:06

Nate

19.1k9 gold badges51 silver badges54 bronze badges

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

1 Comment

jpmc26 Over a year ago

On the contrary, that's gorgeous. Only ugly part is the JSON itself. Here's what it looks like if the JSON is a variable: SELECT value->'time' FROM json_array_elements(my_json->'route_json');.

Collectives™ on Stack Overflow

How to get each JSON child of an array, postgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related