2 ways to use Json_array_elements_text() in PostrgeSQL

user_10042155 0
Viewed 1031

Here are two ways to use json_array_elements_text() method. 1st method:

SELECT id, json_array_elements_text(json_column)
FROM Table

2nd method:

SELECT id, json_index.json_column
FROM Table T
  LEFT JOIN LATERAL json_array_elements_text(json_column) AS json_index(json_column) ON true

I found both methods give the same output, but can we use them interchangeably?

.

postgresql arrays sql json
1 Answers

These two queries happen to produce the same result. The query with the LATERAL join is the explicit (and, in my opinion, better) way to do it.

The documentation describes the first case:

Functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the set-returning function is invoked, and an output row is generated for each element of the function's result set.

[...]

PostgreSQL's behavior for a set-returning function in a query's select list is almost exactly the same as if the set-returning function had been written in a LATERAL FROM-clause item instead.

The documentation then goes on to describe the subtle differences, but they shouldn't matter in your case.

user_64643080
Related