PostgreSQL return a JSON from JSON column with only key/values for which the key is in a python list

Question

Suppose that I have some PostgreSQL table ('table_1') that I want to select from in a Python script. This table has a column with JSON data (called 'json_data') like the following, e.g.:

{'4369': 2, '8465': 1, '12561': 1, '12562': 1}

Now I have a list in Python that looks the following:

[4369, 8465, 12561, 16657]

And my goal is to return a JSON/dict from the postgresql table that only contains key/value pairs for which the key is in the Python list for each row. For the above, I would thus expect a JSON returned as:

{'4369': 2, '8465': 1, '12561': 1}

My guess is that it has to look somewhat like this, but I have no clue what has to go at the '...'

SELECT json_data ->> ... IN 'python_list'
FROM table_1

question from:https://stackoverflow.com/questions/65916759/postgresql-return-a-json-from-json-column-with-only-key-values-for-which-the-key

Answer 1

You can use JSONB_OBJECT_AGG(), JSONB_EACH() and JSONB_ARRAY_ELEMENTS() function together within a SELECT Statement which includes JOINs in order to match elements of the dict object with the keys of JSON object such as

SELECT JSONB_OBJECT_AGG( j1.key, j1.value ) AS js_data
  FROM table_1 t
 CROSS JOIN JSONB_EACH(json_data) AS j1 
  JOIN JSONB_ARRAY_ELEMENTS('[4369, 8465, 12561, 16657]'::JSONB) AS j2
    ON j2.value::INT = j1.key::INT

Demo

P.S: I've preferred JSONB, which stores data in a decomposed binary form and faster to process , data type. For JSON, check out the following

Demo2