CODEWITHSUNDEEP
postgresql
Ellis Valentiner
Ellis Valentiner

Reputation: 2206

Create an index for json_array_elements in PostgreSQL

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?

Upvotes: 0

Views: 776

Answers (1)

Ellis Valentiner
Ellis Valentiner

Reputation: 2206

Found an answer:

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

Upvotes: 1

Related Questions