How to get only the jsonb of specific keys from postgres?

Question

I'm aware that you can remove keys from a jsonb in postgres using something like this

select '{"a": 1, "b": 2, "c":3}'::jsonb -'a';
 ?column?
----------
{"b": 2 "c":3}
(1 row)

Is there a way to only grab specific keys? Like let's say I just want to get the key-value pair of just the 'a' key.

Something like this?

select '{"a": 1, "b": 2}'::jsonb + 'a' + 'b';
 ?column?
----------
{"a": 1, "b": 2}
(1 row)

EDIT: Changed the example to to show that I'd like to grab multiple keys-value pairs from the jsonb and not just one pair.

Mumbleskates · Accepted Answer · 2017-10-02T03:10:46.440

20

You can filter down to a single key fairly easily like so:

jsonb_object(ARRAY[key, jsonb_data -> key])

...or you can filter down to multiple keys:

(SELECT jsonb_object_agg(key, value) FROM jsonb_each(jsonb_data) WHERE key IN ('a', 'b'))

Or on a more complex condition, if you want:

(
  SELECT jsonb_object_agg(key, value)
  FROM jsonb_each(jsonb_data)
  WHERE
    key NOT LIKE '__%'
    AND jsonb_typeof(value) != 'null'
)

These kinds of questions can be answered very easily by simply reading the documentation.

edited Oct 02 '17 at 03:10

answered Sep 29 '17 at 22:00

Mumbleskates

1,248
10
18

Do you know how to grab the jsonb from a specific table? such as `column from table`? – richard_d_sim Sep 30 '17 at 00:17
Yes, these are just example expressions you can use in your returned columns. – Mumbleskates Oct 01 '17 at 05:43
For example, `SELECT jsonb_object(ARRAY['a', jsonb_data -> 'a']) FROM table;` – Mumbleskates Oct 01 '17 at 05:44

score 7 · Answer 2 · answered Oct 02 '17 at 18:31

7

I actually found that this way works to.

select jsonb_build_object('key', column->'key') from table;

reference: https://www.reddit.com/r/PostgreSQL/comments/73auce/new_user_to_postgres_can_i_grab_multiple_keys_of/

answered Oct 02 '17 at 18:31

richard_d_sim

793
2
10
23

2

An important point to mention that you can have multiple keys here like so: `jsonb_build_object('a', column->'a', 'b', column->'b')` – Adam Jun 13 '20 at 11:14

Koushik Das · Answer 3 · 2022-06-04T11:32:53.363

5

You can do this

SELECT jsonb_column->>'key_name_here' as 'alias_name_as_you_like' from table_name

In the case of the query asked above, it would be

select '{"a": 1, "b": 2, "c":3}'::jsonb->>'a'

edited Jun 04 '22 at 11:32

answered Mar 04 '20 at 03:59

Koushik Das

9,678
3
51
50

score 3 · Answer 4 · answered Sep 15 '21 at 15:04

Paraphrasing the situation

we have a jsonb value and multiple keys in mind, a and c
select '{"a": 1, "b": 2, "c":3}'::jsonb - '{a,c}'::text[];
- is a tidy operator but gives us the opposite of what you want
{"b": 2}

solution is to wrap that in array(select jsonb_object_keys(...)) and perform the - again

select '{"a": 1, "b": 2, "c":3}'::jsonb - array(select jsonb_object_keys('{"a": 1, "b": 2, "c":3}'::jsonb - '{a,c}'::text[]));

you get a json with only those keys, a and c

{"a": 1, "c": 3}

score 2 · Answer 5 · answered Sep 29 '17 at 19:11

2

You can get just the value like so:

 select '{"a": 1, "b": 2}'::jsonb-> 'a';

If you must, you can transform that back into jsonb manually, or perhaps go through an array, hstore or other intermediate type. Here's the "manual" way

 select ('{ "a": '||('{"a": 1, "b": 2}'::jsonb->'a')::text||'}')::jsonb

answered Sep 29 '17 at 19:11

Joe Love

5,594
2
20
32

That does work, is there anyways to grab multiple keys without needing to store it within a new select variable? Like just to have it be a new jsonb? – richard_d_sim Sep 29 '17 at 21:17
You should not be reconstructing json/jsonb objects like this. There are functions available [in the documentation](https://www.postgresql.org/docs/current/static/functions-json.html) to do what you are trying to do, namely `jsonb_object()`. – Mumbleskates Sep 29 '17 at 21:35

score 0 · Answer 6 · answered Sep 06 '19 at 02:58

If you want to filter multiple rows with JSONB documents in each of them:

-- Let's generate rows with JSONB column:
WITH s AS (SELECT generate_series(1, 100) num),
g AS (SELECT num, jsonb_build_object('a', s.num, 'b', s.num * 2) obj FROM s),

-- A "filter" adding (in my example only keys of "filter" document remain in result rows)
j AS (SELECT '{"a": "int", "c": "string"}'::jsonb AS filter),
a AS (SELECT (ARRAY(SELECT jsonb_object_keys(filter))) AS ar FROM j),

-- Useless keys removing
o AS (SELECT jsonb_object_agg(l.key, l.value) obj
        FROM g, LATERAL jsonb_each(g.obj) l, a 
        WHERE l.key = ANY(a.ar)
        GROUP BY num)

SELECT * FROM o ORDER BY obj->'a';

score 0 · Answer 7 · answered Sep 29 '21 at 09:07

Begin;
    CREATE TEMPORARY TABLE test (id  serial, jdoc jsonb);
    insert into test(jdoc) values('{"a": {"b":"foo"}}');
    insert into test(jdoc) values('{"a": "test"}');
    insert into test(jdoc) values('{"a":[2,3,4]}');
    insert into test(jdoc) values('{"b":[2,3,4]}');
  commit;

select (jdoc->'a') from test where jdoc ? 'a'
will get all the specific key's value.
If you want JSONB of the specific key: select jdoc from test where jdoc ? 'a'

How to get only the jsonb of specific keys from postgres?

7 Answers7

Linked