Postgres 10, select from json object containing json array of objects

Question

In the database I have text field contains json with the structure:

"Limits":{
  "fields":[
    {
      "key":"DAILY_LIMIT",
       "value":"1559",
       "lastModified":1543857829148,
    },
    {
      "key":"MONTHLY_LIMIT",
      "value":"25590",
      "lastModified":1543857829148,
    }
  ]
}

I need to check if daily_limit exists. It's easy to do with LIKE %DAILY_LIMIT% but performance is not so good and also I won't have access to value (right now I don't need it but maybe in future, it'll be needed). There is an option to check if this key exists without killing the db? I tried with 'Limits'->'fields'-> but I don't know what should be next... And it must be done by query, I cant pass object to backend and then check it

If the LIKE performance is bad, it will be much worse to cast it to JSON, and then perform some JSON functions on it. You will not get better than LIKE with your table schema as it is. — 404
– 404, Commented Dec 3, 2018 at 23:30

S-Man · Accepted Answer · 2018-12-04 07:41:41Z

1

demo: db<>fiddle

If you want to do it the JSON way this could be a solution:

WITH data AS (
   SELECT 'somedata' as somedata, '{"Limits":{"fields":[{"key":"DAILY_LIMITS","value":"1559","lastModified":1543857829148},{"key":"MONTHLY_LIMIT","value":"25590","lastModified":1543857829148}]}}'::jsonb as data
)
SELECT 
    d.*
FROM data d, jsonb_array_elements(data -> 'Limits' -> 'fields')
WHERE value ->> 'key' = 'DAILY_LIMITS'

jsonb_array_elements expands the array into one row each element. In the next step you are able to check the key's value.

But the demo shows, that a simple LIKE would be much faster as @404 mentioned correctly (have a look at the costs of both examples.)

answered Dec 4, 2018 at 7:41

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

2 Comments

Rafał Over a year ago

So for example, if I want to check key and value (let's say it's a timestamp) value ->> 'key' = 'DAILY_LIMITS' AND value ->> 'value' > current_timestamp should work? And maybe a stupid question, but how to return value of 'value'? I'll check it in an hour, thanks a lot!

S-Man Over a year ago

Does this help? dbfiddle.uk/… (A) The comparison would work if "value" would have a timestamp. And you need a cast because at the moment it's a text. (B) Simply take name "value"

Collectives™ on Stack Overflow

Postgres 10, select from json object containing json array of objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest