Question 89117 in PostgreSQL Question posted in
The official documentation can be found here.

Convert select Oracle query to support PostgreSQL

Oracle Query –

In Oracle, restrict is of varchar data type with check constraint of json type, and we are trying to get the result where the informationType = ‘MEASURE’ and accessScope = ‘NONE’ in the column value of table.

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 query-

In PostgreSQL, restrict is of text data type, and we are trying to get the result where the informationType = ‘MEASURE’ and accessScope = ‘NONE’ in the column value of table, but getting cannot extract elements from a scalar

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';

But getting below error –

cannot extract elements from a scalar
SQL state: 22023

Could anyone please help to rewrite the query to get the desired result.

Tags:

2

Answers


  1. 0

    As far as I understand the logic of your Oracle query, this should be it.

    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';
    Login or Signup to reply.
  2. 0

    I am not entirely sure, but it looks like you don’t need to turn the array into rows at all. You can apply a JSON path expression in the WHERE clause which might also remove the need to apply DISTINCT:

    select u_id
from u_restrict u
where u.restrict::jsonb @? '$[*] ? (@.informationType == "MEASURE" && @.accessScope == "NONE")'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search