Skip to content
Advertisement

PostgreSQL json_array_elements with array indexes (keys)

Simple query works fine:

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

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:

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

UPD2

Solution using row_numbers():

Thanks in advance!

Advertisement

Answer

Use the function json_array_elements_text() with ordinality:

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement