2021-03-30

How to get result back as JSON in looped query?

I am looping and getting results from multiple tables, and forming a result. How do I get the result back? All I am doing right now is logging it. Also, when I use array_append, it is converting the json into string.

This is the code I am running to get the data.

do
$$
    DECLARE
        sampleproductId   varchar;
        productIds        text[] := array [
            'abc1',
            'abc2'
            ];
        tId              varchar;
        DECLARE result  jsonb;
        DECLARE resultS jsonb[];
    begin
        FOREACH sampleproductId IN ARRAY productIds
            LOOP
                tId := (select id
                        from product.product
                        where uid = sampleproductId);
                result = (select row_to_json(row)
                           from (select accountid as "accountid", tId as "productUID", sampleproductId as "sampleproductId"
                                 from product.accountproductmap
                                 where productId = cast(tId as int)) row);
                if (result is not null) then
                    resultS = array_append(resultS, result);
                end if;
            END LOOP;
        RAISE NOTICE 'Result: %', resultS;
    end ;
$$;

And the result I am getting is

{"{\"accountid\": 8133, \"productUID\": \"1685\", \"sampleproductId\": \"abc1\"}","{\"accountid\": 9034, \"productUID\": \"2114\", \"sampleproductId\": \"abc2\"}"}

I am not sure why array_append is converting it into string before adding it, and not just keeping it as json.

I am looking to get it formatted like this

[
  { "accountid": 8133, "productUID": 1685, "sampleproductId": "abc1" },
  { "accountid": 9034, "productUID": 2114, "sampleproductId": "abc2" }
]


from Recent Questions - Stack Overflow https://ift.tt/2PH06Ag
https://ift.tt/eA8V8J

No comments:

Post a Comment