将select Oracle查询转换为支持PostgreSQL

Question

Oracle Query -

在Oracle中，restrict是varchar数据类型，检查约束是json类型，我们试图得到的结果是表的列值informationType = 'MEASURE'和accessScope = 'NONE'

select distinct u_id
from u_restrict u,
json_table(u.restrict, '$[*]' columns (informationType varchar2 path '$.informationType',
accessScope varchar2 path '$.accessScope')) jt 
where jt.informationType = 'MEASURE' and jt.accessScope = 'NONE'

PostgreSQL查询——

在PostgreSQL中，restrict是文本数据类型，我们试图获得表列值中informationType = 'MEASURE'和accessScope = 'NONE'的结果，但是get无法从标量中提取元素

SELECT distinct u_id
FROM u_restrict u,
jsonb_array_elements(u.restrict::jsonb) AS r,
jsonb_array_elements(r->'informationType') AS ir,
jsonb_array_elements(ir->'accessScope') AS ase
WHERE ase->>'accessScope' = 'NONE'
AND ir->>'informationType' = 'MEASURE';

但低于错误-

不能从标量中提取元素
SQL state: 22023

谁能帮助重写查询，以获得所需的结果。

Answer 1

我不完全确定，但看起来您根本不需要将数组转换为行。您可以在WHERE子句中应用JSON路径表达式，这也可能消除应用DISTINCT的需要:

select u_id
from u_restrict u
where u.restrict::jsonb @? '$[*] ? (@.informationType == "MEASURE" && @.accessScope == "NONE")'

Answer 2

就我所理解的Oracle查询逻辑而言，应该是这样的。

SELECT distinct u_id
FROM u_restrict u
CROSS JOIN LATERAL jsonb_array_elements(u.restrict::jsonb) l(r)
WHERE r ->> 'accessScope' = 'NONE' 
AND r ->> 'informationType' = 'MEASURE';