Get element by unknown key from jsonb in Postgres

Question

I have following data structure:

{
    "proccess1": {
        "error": "error1 description",
        "nextRetryAt": "2018-02-22T07:39:00.325Z",
        "attemptsMade": 148,
        "firstFailedAt": "2018-02-16T06:40:41.327Z"
    },
    "proccess2": {
        "error": "error2 description",
        "nextRetryAt": "2019-03-16T06:41:01.566Z",
        "attemptsMade": 77,
        "firstFailedAt": "2016-03-15T04:35:12.248Z"
    }
}

My question is how to build query like

select * 
from data 
where data->[0]->>'nextRetryAt' = 'my passed value'

I don't know the names of keys proccess1 and proccess2. They might have any values.

Vao Tsun · Accepted Answer · 2018-02-22 09:06:13Z

11

https://www.postgresql.org/docs/current/static/functions-json.html

jsonb_object_keys will help you

https://www.db-fiddle.com/f/9fB1pfb3BWv4v2wBUwU1Bd/0

with c(j) as (values('{
    "proccess1": {
        "error": "error1 description",
        "nextRetryAt": "2018-02-22T07:39:00.325Z",
        "attemptsMade": 148,
        "firstFailedAt": "2018-02-16T06:40:41.327Z"
    },
    "proccess2": {
        "error": "error2 description",
        "nextRetryAt": "2019-03-16T06:41:01.566Z",
        "attemptsMade": 77,
        "firstFailedAt": "2016-03-15T04:35:12.248Z"
    }
}'::jsonb))
select j->jsonb_object_keys(j)->>'nextRetryAt' from c;

answered Feb 22, 2018 at 9:06

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

Get element by unknown key from jsonb in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related