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

Barbaros Özhan · Accepted Answer · 2021-01-27 10:52:13Z

2

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

edited Jan 27, 2021 at 10:52

answered Jan 27, 2021 at 10:30

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

3 Comments

Frank Heikens Over a year ago

JSONB makes imho only sense when you need indexes on this data as well, for faster searching. Otherwise it takes more storage space and is slower on INSERT's. I don't think our examples can use an index.

Barbaros Özhan Over a year ago

you're right @FrankHeikens , that will depend on the OP'S choice.

Josh Over a year ago

With some tweaking, this gets me where I have to be. Both good answers though :)

Frank Heikens · Accepted Answer · 2021-01-27 10:21:18Z

1

Example, just in plain SQL and using a CTE to mimic your table:

WITH i AS (
    SELECT  '{"4369": 2, "8465": 1, "12561": 1, "12562": 1}'::json AS json_data
)
SELECT  *
FROM    i, -- your table name
        json_each(json_data)
WHERE   key::int =any(ARRAY[4369, 8465, 12561, 16657]);

The function json_each (or jsonb_each when using jsonb data type) does the trick.

I hope you can transform this into something useful in python.

answered Jan 27, 2021 at 10:21

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

2 Comments

Barbaros Özhan Over a year ago

OP clearly states that the expected result is of the form {'4369': 2, '8465': 1, '12561': 1}

Frank Heikens Over a year ago

@BarbarosÖzhan: You're right, forgot that part

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related