CODEWITHSUNDEEP
sqlarraysjsonpostgresql
Eugene Burdeinyi
Eugene Burdeinyi

Reputation: 23

PostgreSQL json_array_elements with array indexes (keys)

Simple query works fine:

SELECT json_array_elements_text('["first", "third", "second"]'::json)

But I also want to retrieve array keys somehow so the output would be like:

key    value
 0     first
 1     third
 2     second

UPD

Seems like row_number() is a p̶r̶o̶p̶e̶r̶ solution, but I cannot figure out how to use it further.

Lets say i have 'posts' table, each post contains an array of related comments in JSON format:

SELECT id, title, comments FROM posts

id     title             comments
 1    Title 1  ["comment 1", "comment 2"]
 2    Title 2  ["comment 3", "comment 4", "comment 5"]
 3    Title 3  ["comment 6"]

The goal is to expand not only comments values, but also the keys:

Tricky SQL here

id     title    comment   key
 1    Title 1  comment 1   0
 1    Title 1  comment 2   1
 2    Title 2  comment 3   0
 2    Title 2  comment 4   1
 2    Title 2  comment 5   2
 3    Title 3  comment 6   0

UPD2

Solution using row_numbers():

SELECT *, row_number() OVER (PARTITION BY id) - 1 AS key
FROM (
  SELECT id, title, json_array_elements_text(comments::json) AS comment
  FROM posts
) p

Thanks in advance!

Upvotes: 2

Views: 6804

Answers (2)

klin
klin

Reputation: 121494

Use the function json_array_elements_text() with ordinality:

with my_table(id, title, comments) as (
values
(1, 'Title 1', '["comment 1", "comment 2"]'::json),
(2, 'Title 2', '["comment 3", "comment 4", "comment 5"]'),
(3, 'Title 3', '["comment 6"]')
)

select id, title, value as comment, ordinality- 1 as key
from my_table
cross join json_array_elements_text(comments) with ordinality

 id |  title  |  comment  | key 
----+---------+-----------+-----
  1 | Title 1 | comment 1 |   0
  1 | Title 1 | comment 2 |   1
  2 | Title 2 | comment 3 |   0
  2 | Title 2 | comment 4 |   1
  2 | Title 2 | comment 5 |   2
  3 | Title 3 | comment 6 |   0
(6 rows)

From the documentation:

If the WITH ORDINALITY clause is specified, an additional column of type bigint will be added to the function result columns. This column numbers the rows of the function result set, starting from 1.

Upvotes: 10

Jakub Kania
Jakub Kania

Reputation: 16477

JSON arrays don't have keys, however you can get the desired output by using rownum:

SELECT row_number() OVER ()-1 AS key, *
FROM json_array_elements_text('["first", "third", "second"]'::json) q

sqlfiddle

Upvotes: 0

Related Questions