postgresql how to select a value from multiple jsons inside a array on a jsonB collumn-CodePudding

So, I have this table

create table <table_name>(attr jsonb)

And this is the data inside

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

what I want is to select both names, what I have accomplished so far is this

select attr -> 'rules' -> 0 -> 'name' from <table_name>;

which returns test_01

select attr -> 'rules' -> 1 -> 'name' from <table_name>;

which returns test_02

I want to return something like this:

test_01,test_02

or if it's possible to return them in multiple lines, that would be even better

This is a sample data to show my problem, for reasons beyond my control, it's not possible to store each rule on a distinct line

CodePudding user response：

if anyone else get stuck on a situation like this, this is the solution the I found

create or replace function func_get_name() RETURNS text
language 'plpgsql'
AS $$
declare 
    len character varying(255);
    names character varying(255);
    res character varying(255);
   
begin 
    select jsonb_array_length(attr->'rules') into len from <table_name>;
    res := '';

    for counter in 0..len loop
        
        select attr->'rules'-> counter ->> 'name'
        into names
        from <table_name>;
    
        if names is not null then
        res := res || ' ' || names;
        end if;
    
    end loop;
    return res;
end;
$$

select func_get_name();

it's a solution: yes, it's a good solution: I have no ideia

CodePudding user response：

You can use jsonb_array_length together with generate_series to get each name. Then use string_agg to aggregate list of names. Without plpgsql and with a single statement. (see demo)

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;

Alternative:

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 
             join ( select generate_series(0,counter-1) from jl ) gs(n) 
               on true 
          ) rn;