CODEWITHSUNDEEP
sqljsonpostgresql
Suraj
Suraj

Reputation: 2477

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]

Upvotes: 0

Views: 1935

Answers (1)

user330315
user330315

Reputation:

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

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

Upvotes: 3

Related Questions