Postgres JSONB - query all root keys

user_11672684 0
Viewed 179

PostgreSQL (13.2)

I have a table with 2 columns - timestamp and snapshot. Snapshot consist a JSONB - dict that represent the condition of warehouse. The data stracture is like this

{
    "key1": {...},
    "key2": {...},
    "key3": {...},
    "key4": {...},
    "key5": {...}
}

So I need to get the specific key in json that consist the data I am looking for. I also need to get a row timestamp that holds the snapshot. How can I query this?

ps. In python i can use smth like dict.keys and then start looping across all keys, something similiar i need here for searching the data.

pps. also It would be interesting to know how to implement it with SQLAlchemy

[screenshot from DB viewer]

1

sqlalchemy postgresql python json
1 Answers

(jsonb_each()).* selects all keys and values from jsonb field:

SELECT (JSONB_EACH(blocks)).*
FROM snapshots

then we need to get values by nested keys:

SELECT key AS rack_num
     , value ->> 'client_id' AS client_id
     , value ->> 'timestamp' AS _TIMESTAMP
FROM (
      SELECT (JSONB_EACH(blocks)).*
      FROM snapshots
     ) AS t

and finally, aggregate the results:

SELECT rack_num
       , MIN(_TIMESTAMP) AS appearance_time
FROM (
      SELECT KEY                   AS rack_num
           , VALUE ->> 'client_id' AS client_id
           , VALUE ->> 'timestamp' AS _TIMESTAMP
      FROM (
            SELECT (JSONB_EACH(blocks)).*
            FROM snapshots
           ) AS t
      ) AS racks
WHERE client_id = :client_id
GROUP BY rack_num, client_id
ORDER BY MIN(_TIMESTAMP) DESC;
user_80300920
Related