Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table table_name(attr jsonb); 

insert into table_name ( attr) 
values (
'{
  "rules": [
    {
      "id": "foo",
      "name": "test_01"
    },
   {
      "id": "bar",
      "name": "test_02"
    }
  ]
}'
);

select * 
  from table_name; 
1 rows affected



attr


{"rules": [{"id": "foo", "name": "test_01"}, {"id": "bar", "name": "test_02"}]}


with jl(counter) as 
     ( select jsonb_array_length(attr->'rules') from table_name ) 
select string_agg(name,' ') "Rule Names" 
  from (select attr->'rules'-> n ->> 'name' name
          from table_name 
          cross join ( select generate_series(0,counter-1) from jl ) gs(n) 
       ) rn; 


Rule Names


test_01 test_02