PostgreSQL - return jsonb keys in one column

Question

I have the query to search for ids in jsonb column, the array can contain many id's.

Say I have data like this

id | act | act_id |                    from_ids                         |    object_ids       | post_date
2    post      1    {"2":"1494308197","3":"1494308198","4":"1494308199"}  {"items":["104564"]}   1494308197

And a query like this

SELECT an.*
FROM activity_network an
WHERE an.from_ids ?| ARRAY['2','3'];

That query will return the row because it finds 2 and 3. But how can I return what it finds in it's own column. So that it returns 2,3 in text or json format or something like that in the results as well.

I tried this

SELECT an.*, jsonb_each_text(from_ids) b
FROM activity_network an
WHERE an.from_ids ?| ARRAY['2','3'];

But that creates 3 rows with a b column each one with the value 2, 3 and 4. I want 1 row with b column containing both 2 and 3 which is what I searched on.

Is that possible?

example result that I'm looking for. notice the last column. I put it as column delimited for demo purpose. it can be any format I can use.

2 | post | 1 | {"2":"1494308197","3":"1494308198","4":"1494308199} | {"items":["104564"]} | 1494308197 | 2,3}

you want to show you json with keys 2 and 3 only, ommiting the key 4 - right?.. — Vao Tsun
– Vao Tsun, Commented May 9, 2017 at 8:22
Yes, so for instance I have an array of ids that I search on, this case being 2 and 3, the jsonb field also contains key with 4. But I just want to know the ids the query found the record with which in this example is 2 and 3. — Panama Jack
– Panama Jack, Commented May 9, 2017 at 8:24

Vao Tsun · Accepted Answer · 2017-05-09 08:36:19Z

2

here I explode/implode it. Quite ugly way.

t=# with p as (
  with c as (
    select '{"2":"1494308197","3":"1494308198","4":"1494308199"}'::json j
  )
  select json_object_keys(j),j->json_object_keys(j) v
  from c
)
select concat('{',string_agg(concat('"',json_object_keys,'"',':',v)::text,','),'}')::json
from p
where json_object_keys::int = ANY (ARRAY [2,4]);
               concat
-------------------------------------
 {"2":"1494308197","4":"1494308199"}
(1 row)

Time: 0.348 ms

answered May 9, 2017 at 8:36

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Comments

coladict · Accepted Answer · 2017-05-09 08:39:36Z

The function jsonb_exists_all sounds like what you want. It requires that all the elements in the array exist as top-level keys in the object.

You can find that and other undocumented functions for jsonb using the \df *jsonb* command in psql.

example paste:

test=# SELECT * from twj WHERE jsonb_exists_any(from_ids, ARRAY['2','3']);
 id | act  |                         from_ids                          
----+------+-----------------------------------------------------------
  1 | post | {"2": "1494308197"}
  3 | post | {"2": "1494308197", "3": "1494308198", "4": "1494308199"}
(2 rows)

test=# SELECT * from twj WHERE jsonb_exists_all(from_ids, ARRAY['2','3']);
 id | act  |                         from_ids                          
----+------+-----------------------------------------------------------
  3 | post | {"2": "1494308197", "3": "1494308198", "4": "1494308199"}
(1 row)

The ?| operator you are using calls the jsonb_exists_any function.

Collectives™ on Stack Overflow

PostgreSQL - return jsonb keys in one column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related