Postgres jsonb_array_elements

Question

I have a table with the next jsonb field -

{ "auth": [{"roles": ["role1", "role2"]}]}

When I do this query -

select jsonb_array_elements(role) as role from (
    select x -> 'roles' as role
    from test,
         jsonb_array_elements(data->'auth') x

the output format is

role
----
"role1"
"role2"

The current problem is that if I try to add where it doesn't work

x.role = '"role1"', but this one does work x.role like '%"role1"%'

Thanks

Vicctor · Accepted Answer · 2021-09-08 15:23:12Z

2

It is possible to restore "role" field as text data using ->> operator.

create table test (data jsonb);

insert into test values('{ "auth": [{"roles": ["role1", "role2"]}]}');

with cte as (
   select jsonb_array_elements(role) ->> 0 as role from 
     (
       select x -> 'roles' as role
              from test, jsonb_array_elements(data->'auth') x) y)
select role from cte where role = 'role1';

answered Sep 8, 2021 at 15:23

Vicctor

8236 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2021-09-08 15:24:17Z

1

The problem is that jsonb_array_elements returns the results as jsonb, and you get in trouble comparing that to a string literal.

Use jsonb_array_elements_text instead of the first jsonb_array_elements call, then the results will be of type text, which should give you no trouble.

answered Sep 8, 2021 at 15:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Postgres jsonb_array_elements

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related