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.
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} .
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.
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.
jsonb elements can be of the following types: Objects. Arrays.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With