Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres: expand JSON array elements into values with their index?

I have a table with a column containing a JSON array. As an example:

with example as (
    select '["a", "b"]'::jsonb as col
    union select ('["c", "d", "e"]'::jsonb) as col
)
select col from example

Returns:

col
["a", "b"]
["c", "d", "e"]

I can use jsonb_array_elements to expand out each array into rows:

select jsonb_array_elements(col) from example

Returns:

jsonb_array_elements
"a"
"b"
"c"
"d"
"e"

I want the index of each array element along with the element itself (a bit like Python's enumerate), like so:

jsonb_array_elements    array_index
"a"                     1
"b"                     2
"c"                     1
"d"                     2
"e"                     3

How can I do this?

My application has read-only access, so I cannot create functions.

like image 585
Tom Phillips Avatar asked Feb 16 '18 09:02

Tom Phillips


People also ask

How do you update objects in Jsonb arrays with PostgreSQL?

Postgres offers a jsonb_set function for updating JSON fields. The second parameter path defines, which property you want to update. To update items in an array, you can use an index-based approach. To update the first entry in the items array in the example above, a path woud look like this: {items, 0, customerId} .

How do I query Jsonb data in PostgreSQL?

Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.

What is Jsonb in Postgres?

JSONB stands for “JSON Binary” or “JSON better” depending on whom you ask. It is a decomposed binary format to store JSON. JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.

Can Jsonb be an array?

jsonb elements can be of the following types: Objects. Arrays.


1 Answers

Use with ordinality:

with example (col) as (
  values 
    ('["a", "b"]'::jsonb),  
    ('["c", "d", "e"]'::jsonb)
)
select t.*
from example, jsonb_array_elements(col) with ordinality as t(e,idx)

returns:

e   | idx
----+----
"a" |   1
"b" |   2
"c" |   1
"d" |   2
"e" |   3

with ordinality can only be used if you use the set returning function in the from clause, which is highly recommended anyway.

like image 181
a_horse_with_no_name Avatar answered Oct 21 '22 00:10

a_horse_with_no_name