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
Comments
Post a Comment