Skip to content
Advertisement

PostgreSQL : Selecting all values of a key in jsonb

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]

Advertisement

Answer

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

select jsonb_path_query_array(agent, '$.key')
from the_table;
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement