Как вернуть результат в виде JSON в зацикленном запросе?

Я зацикливаю и получаю результаты из нескольких таблиц и формирую результат. Как мне вернуть результат? Все, что я сейчас делаю, это регистрирую. Кроме того, когда я использую array_append, он преобразует json в строку.

Это код, который я использую для получения данных.

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 ;
$$;

И в результате я получаю

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

Я не уверен, почему array_append преобразует его в строку перед добавлением, а не просто сохраняет его как json.

Я хочу отформатировать его вот так

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

Используйте unnest, чтобы сделать это с помощью простого SQL. Также опишите, что вы имеете в виду под преобразование в строку и сохраняя как json. JSON удобочитаем, поэтому я не вижу разницы между json и некоторым текстовым выводом в соответствующем формате.

— 30.03.2021 03:11

@astentx Спасибо. Я добавил ожидаемый ответ.

— 30.03.2021 03:17

@astentx Я никогда раньше не использовал unnest. Где мне его использовать? Я просто попробовал выбрать * из unnest (результаты), и это не сработало.

— 30.03.2021 03:21

sql postgresql

30.03.2021 02:57

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Ответы 1

Вы можете сделать это с помощью простого SQL, потому что

массивы легко конвертируются в табличный результат с помощью функции разложить. Таким образом, вы можете вводить данные для запроса.
SQL был разработан для эффективной и декларативной работы с множествами (а не с помощью простых циклов). Вот почему вам следует использовать join вместо петель.

Для сериализации данных в json вы можете использовать jsonb_agg. Итак, это запрос:

create table product
as
select *
from (
values
  (1, 'abc1'),
  (2, 'abc2'),
  (3, 'def1'),
  (4, 'def2')
) as t (id, uid)

create table accountproductmap
as
select *
from (values
  (1, 123),
  (2, 456),
  (3, 789)
) as t(productid, accountid)

with sp as (
select *
from unnest('{
  abc1,
  abc2,
  abc\,3
}'::varchar(10)[]
) sampleproductid
)
select jsonb_agg(
  jsonb_build_object(
    'accountid', a.accountid,
    'productUID', p.id,
    'sampleproductId', p.uid
)) as q
from sp
  join product as p
    on sp.sampleproductid = p.uid
  join accountproductmap as a
    on p.id = a.productid

| q                                                                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------- |
| [{"accountid": 123, "productUID": 1, "sampleproductId": "abc1"}, {"accountid": 456, "productUID": 2, "sampleproductId": "abc2"}] |

db <> рабочий пример здесь

31.03.2021 21:28