Create an index for json_array_elements in PostgreSQL

Question

I need to create an index from a query that uses json_array_elements()

SELECT *, json_array_elements(nested_json_as_text::json) as elements FROM my_table

Since the json contains multiple elements, the result is that the original index is now duplicated across rows and no longer unique.

I am not very familiar with creating indices and want to avoid doing anything destructive. What is the best way to create a column of unique integers for this case?

Ellis Valentiner · Accepted Answer · 2015-04-06 19:09:07Z

1

Found an answer:

SELECT *, json_array_elements(nested_json_as_text::json) as elements, row_number() over () as my_index FROM my_table

answered Apr 6, 2015 at 19:09

Ellis Valentiner

2,2144 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create an index for json_array_elements in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related