Yes, there is a guarantee. But it s a weak one.
<代码>id和 姓名将留守(同附标的)辛迪加,因为对两个固定的机组功能的评价是“锁定”。 手册:
If there is more than one set-returning function in the query s select
list, the behavior is similar to what you get from putting the
functions into a single LATERAL ROWS FROM( ... ) FROM-clause item.
For each row from the underlying query, there is an output row using
the first result from each function, then an output row using the
second result, and so on. If some of the set-returning functions
produce fewer outputs than others, null values are substituted for the
missing data, so that the total number of rows emitted for one
underlying row is the same as for the set-returning function that
produced the most outputs. Thus the set-returning functions run “in
lockstep” until they are all exhausted, and then execution continues
with the next underlying row.
Bold emphasis mine.
Since both your calls are guaranteed to return the same number of rows, this even works reliably before Postgres 10 (where the behavior of multiple set-returning functions in the SELECT list was reformed (sanitized). See:
Unlike json, jsonb has a deterministic sort order of nested elements. But the only relevant aspect here is the order of array items, and that is always significant and preserved in json and jsonb alike.
Optimize query
Elaborating on what we just learned about the internal workings, your query:
SELECT jsonb_array_elements(data) -> id AS id
, jsonb_array_elements(data) -> name AS name
FROM tbl;
......
SELECT t.id, x.i-> id AS id, x.n-> name AS name
FROM tbl t
CROSS JOIN LATERAL ROWS FROM (jsonb_array_elements(t.data), jsonb_array_elements(t.data)) x(i, n);
更显而易见的是,应当将其简化到一个分局的单一职能中。 然后,田地不能从yn开始:
SELECT t.id, x-> id AS id, x-> name AS name
FROM tbl t
CROSS JOIN LATERAL jsonb_array_elements(t.data) x;
Or, with minimal syntax:
SELECT id, x-> id AS id, x-> name AS name
FROM tbl, jsonb_array_elements(data) x;
https://dbfiddle.uk/zfPSGxCj>rel=“nofollow noreferer”>fiddle
当把多个不同的领域从同一职能要求返回时,该职能将按所示顺序排列。 重复评价风险(成本) 见:
True issue with (multiple) SRF in the SELECT list
以上所有内容均载于 。 如果在<代码>SlectT清单中填写的SRF不退还一行(如<代码>jsonb_array_elements(>>发现空阵,则该行从结果中删除。 当<代码>SlectT清单中包含多个SRF时,当所有这些系统都空出时,就会删除。 这可能导致令人惊讶的结果。 从我的经验来看,很少有人意识到这一微妙的影响。
If that side effect is intended, it s much clearer spelled out as CROSS JOIN as demonstrated above.
If that side effect is not intended, and you d rather preserve all rows, use LEFT JOIN LATERAL ... ON true instead:
SELECT t.id, x-> id AS id, x-> name AS name
FROM tbl t
LEFT JOIN LATERAL jsonb_array_elements(t.data) x ON true;
https://dbfiddle.uk/zfPSGxCj>rel=“nofollow noreferer”>fiddle
www.un.org/Depts/DGACM/index_spanish.htm 使用这一查询,除非你更好地了解情况。 见:
Aside: jsonb_populate_recordset() for this particular query
具体询问,jsonb_populate_recordset():快捷且含蓄的类型。
如果你没有:
CREATE TYPE my_rowtype AS (id int, name text);
然后:
SELECT t.id, x.id, x.name
FROM tbl t
LEFT JOIN LATERAL jsonb_populate_recordset(null::my_rowtype, t.data) x ON true
https://dbfiddle.uk/HvH5NPrC”rel=“nofollow noreferer”>fiddle
相关:
