PostgreSQL - query the value for Nth instance of key in JSONB data

Question

Sorry for the formatting on this. There's too much text to create a readable table, so bear with me.

I have a table called 'declines'. It has two columns: 'id' and 'dec_reasons'.

For id = 34254, this is the 'dec_reasons' value in JSONB format:

[
    {
        "id": 94748,
        "reason": "Lead Fico Threshold is not Greater Than Or Equal To 500",
        "created_at": "2019-05-02T07:57:59.706448",
        "decline_code": "fico_too_low",
        "leaf_node_id": 7,
        "decision_type": "credit",
        "triggers_noaa": true,
        "general_description": "FICO score is too low"
    },
    {
        "id": 94747,
        "reason": "Fico score is very low",
        "created_at": "2019-05-02T07:57:59.705578",
        "decline_code": "fico_too_low",
        "leaf_node_id": 5,
        "decision_type": "credit",
        "triggers_noaa": true,
        "general_description": "FICO score is too low"
    }
]

For id = 34257, this is the 'dec_reasons' value in JSONB format:

[
    {
        "id": 94772,
        "reason": "Lead Fico Threshold is not Greater Than Or Equal To 500",
        "created_at": "2019-05-02T07:58:05.988900",
        "decline_code": "fico_too_low",
        "leaf_node_id": 7,
        "decision_type": "credit",
        "triggers_noaa": true,
        "general_description": "FICO score is too low"
    },
    {
        "id": 94771,
        "reason": "Fico score is very low",
        "created_at": "2019-05-02T07:58:05.964931",
        "decline_code": "fico_too_low",
        "leaf_node_id": 5,
        "decision_type": "credit",
        "triggers_noaa": true,
        "general_description": "FICO score is too low"
    }
]

In each 'dec_reasons' value, there are multiple instances of the 'reason' key. How can I query for the value paired with the 1st instance of 'reason' in each 'dec_reasons' value?

If I queried the for the first instance of the 'reason' key, I'd want to see:

'Lead Fico Threshold is not Greater Than Or Equal To 500'

If I queried the for the second instance of the 'reason' key, I'd want to see:

'Fico score is very low'

Schwern · Accepted Answer · 2019-07-26 18:23:27Z

1

To query within a jsonb structure you can either use -> to drill down. This fetches the value of key in the second entry.

# select '[{"key": 10},{"key": 20},{"key": 30}]'::jsonb->1->'key';
 ?column? 
----------
 20
(1 row)

Or you can use #> to directly query a path.

# select '[{"key": 10},{"key": "20"},{"key": 30}]'::jsonb#>'{1,"key"}';
 ?column? 
----------
 "20"
(1 row)

See json and jsonb Operators in the Postgres docs.

answered Jul 26, 2019 at 18:23

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - query the value for Nth instance of key in JSONB data

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