Issue with Appending a JSON Array in a PostgreSQL Function

Question

I'm encountering an issue when attempting to append a JSON array or object into another JSON array within a PostgreSQL function. It seems that the array_append function is inserting the JSON as a string, resulting in an unexpected format in the output.

Currently, I'm getting output like this:

{"{"category_id":8,"category_name":"08 Candy","is_active":true,"category_name_app":"Candy","display_order":7}"} However, I'd like the output to be in this format so that I can easily decode it in my code:

[{"category_id":8,"category_name":"08 Candy","is_active":true,"category_name_app":"Candy","display_order":7}]

Below is the logic of my function:

    for all_categories in select * from categories where is_active = '1' loop
            show_at_homepage = 0;
        
            for current_subcat in select * from public."V_category_to_sub_category_w_names" where category_id = all_categories.category_id and sub_category_is_active = '1' loop
                
                
                select * into product_count from public."V_APP_products_w_sub_categories" where sub_category_id = current_subcat.sub_category_id and store_id = get_store_id and is_deleted='0';
                if count(product_count) > 0 then
                    
                    show_at_homepage = 1;
                                
                
                end if;
                
            end loop;
            
            if show_at_homepage = 1 then
                    select row_to_json(all_categories) into cat_json;
                    
                    select array_append(my_json_result_array,cat_json) into my_json_result_array;
                end if;
        
        
    end loop;
    return my_json_result_array;

Edouard · Accepted Answer · 2021-11-21 09:25:52Z

1

You can apply array_append to any kind of array of postgres data types, including json[], but you can't apply it to a json array which is of type json. In your case, you need to apply the json functions and operators, see the manual, and more specifically the json_build_array function :

Replace

select array_append(my_json_result_array,cat_json) into my_json_result_array

By

select json_build_array(my_json_result_array,cat_json) into my_json_result_array

Or when you use jsonb instead of json which is the manual recommendation :

select jsonb_build_array(my_json_result_array,cat_json) into my_json_result_array

edited Nov 21, 2021 at 9:25

answered Nov 20, 2021 at 22:27

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

4 Comments

deep freeze Over a year ago

This is good but i want to append this jsonb array as an array into my_json_result_array. I can't do it. when i use array_append, it appends cat_json into my_json_result_array_array as string. so even if it shows the brackets like array, it wraps it with double quotes and becomes a string.

Edouard Over a year ago

OK look at the updated answer and tell me if it fits your need.

deep freeze Over a year ago

this was close but when i jsonb_build_array it inserts outside the array. let me explain. what i want is like this [//here will be appended categories as array] but with json_build_array, it gives me like [[{category1}],category2]. i have to append it in the same array as category1

Edouard Over a year ago

Based on your last comment, I come back to my initial answer : SELECT '[]' :: jsonb || my_json_result_array :: jsonb || cat_json :: jsonb will return [my_json_result_array , cat_json] :: jsonb. If this does not answer your question, please provide a full set of data sample : my_json_result_array, cat_json and the expected result after the concatenation.

Collectives™ on Stack Overflow

Issue with Appending a JSON Array in a PostgreSQL Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related