PostgreSQL : Selecting all values of a key in jsonb

Question

This is the current code I'm using.

SELECT
(agent->>0)::jsonb->>'key',
(agent->>1)::jsonb->>'key'
FROM
table;

But it's possible that the jsonb column(agent) contains more than 2 sets of values. Is it possible to write a query without specifying indexing(not including 0,1 in query) and get the result as array_agg using just the key.

Sample jsonb :

[
  {
    "name" : "A",
    "key" : "KA"
  },
  {
    "name" : "B",
    "key" : "KB"
  }
]

My desired output be :

[KA, KB]

Post a sample of jsonb.

Salman Arshad
– Salman Arshad

2021-08-17 11:26:56 +00:00
Commented Aug 17, 2021 at 11:26 — Salman Arshad
– Salman Arshad, Commented Aug 17, 2021 at 11:26
Posted a sample of jsonb @SalmanA, thanks.

Suraj
– Suraj

2021-08-17 11:32:45 +00:00
Commented Aug 17, 2021 at 11:32 — Suraj
– Suraj, Commented Aug 17, 2021 at 11:32

user330315 · Accepted Answer · 2021-08-17 12:01:19Z

3

With Postgres 12 or later, you can use a JSON path query.

select jsonb_path_query_array(agent, '$.key')
from the_table;

answered Aug 17, 2021 at 12:01

user330315

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

2 Comments

Suraj Over a year ago

This works perfectly on my local environment. However when I tried to deploy it for my company, this function wasn't available as the version there is 11.9. Are you aware of any alternative functions I can use for the same, thanks.

Suraj Over a year ago

I asked a new question here : stackoverflow.com/questions/69033703/…

Collectives™ on Stack Overflow

PostgreSQL : Selecting all values of a key in jsonb

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related