Postgres query records where any element in array of objects in JSONB field contains number > or < x

Question

I've searched around but this seems to be a more unique requirement for querying JSONB fields than is common and haven't found a solution yet. My table has a jsonb field called 'plan', the content of which looks like this:

{
    days: [
        {
            summary: {
                total: 100
            }
        }
    ]
}

There could be potentially infinity day objects inside the days array. Is it possible to run a query that selects all rows where 'total' is greater than or less than a given number in at least one of the day objects?

If necessary I can rearrange the table or take the 'summary' data and put it in a postgres array field.

Leo C · Accepted Answer · 2017-09-17 06:13:17Z

1

If I understand your question correctly, you can fetch all rows with total in your jsonb column plan not equal to, say 101, as follows:

SELECT *
FROM my_table t,
    LATERAL jsonb_array_elements(plan->'days') days
WHERE
    (days->'summary'->>'total')::integer != 101;

answered Sep 17, 2017 at 6:13

Leo C

22.5k3 gold badges28 silver badges42 bronze badges

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

1 Comment

Henry Over a year ago

Thanks for the reply. I had already worked it out by moving the summaries to an ARRAY[] row, but this might have been a much simpler fix. I'll try it sometime.

Collectives™ on Stack Overflow

Postgres query records where any element in array of objects in JSONB field contains number > or < x

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