Postgres sql: создание вложенной структуры json в одной таблице?

Я пытаюсь построить вложенную структуру Json из одной таблицы.

Я пытаюсь добиться, чтобы окончательный результат выглядел так:

[
    {
        "contract_number":"GS00F0XXX",
        'category': [
            {
                'cat_id': "874 1",
                'socio': {
                    "eighta":false,
                    "sdvosb":false,
                    "edwosb":false
                }   
            },
            {
                "cat_id":"874 6",
                'socio': {
                    "eighta":false,
                    "sdvosb":false,
                    "edwosb":false,
                }
            }
        ]
    },
    {
        'contract_number': "GS00Q14OAXXX",
        'category': [
            {
                'cat_id': 'OASIS POOL1',
                'socio': ...
            }
        ]
    }  
]

Базовая таблица выглядит примерно так:

contract_type   contract_number duns_number eighta  sdvosb  edwosb  category
MOBIS           GS00F0XXX       5555555     FALSE   FALSE   FALSE   874 1
MOBIS           GS00F0XXX       5555555     FALSE   FALSE   FALSE   874 6
MOBIS           GS00F0XXX       5555555     FALSE   FALSE   FALSE   874 7
OASIS           GS00Q14OAXXX    5555555     FALSE   FALSE   FALSE   OASIS POOL1
OASIS           GS00Q14OAXXX    5555555     FALSE   FALSE   FALSE   OASIS POOL2

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

select 
    json_build_object(
        'contract_number', contract_number,
        'info', json_agg(
            json_build_object(
                    'category_id', category,
                    'eighta',eighta,
                    'sdvosb',sdvosb,
                    'edwosb',edwosb
                )
        )
    )
from contract_vehicle
group by duns_number, contract_number

Это приводит к SQL Error [42803]: ERROR: aggregate function calls cannot be nested

select 
    json_build_object(
        'contract_number', contract_number,
        'info', json_agg(
            json_build_object(
                    'category_id', category,
                    'socio', jsonb_agg(
                        json_build_object(
                            'eighta',eighta,
                            'sdvosb',sdvosb,
                            'edwosb',edwosb
                        )
                    )
                )
        )
    )
from contract_vehicle
group by duns_number, contract_number 

Какую версию Postgres вы используете?

Sorix 31.10.2018 14:49

Я использую Postgres 9.6.

spitfiredd 31.10.2018 15:58

Поддержка @Sorix json была добавлена ​​в postgres с версии 9.3

SABER 31.10.2018 16:01

@SABER - ФИКЦИАЛЬНЫЙ СИМВОЛ json_build_object () поддерживается в 9.4 или новее

Sorix 31.10.2018 16:23
1
4
177
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Я не уверен, что это сработает, но попробуйте :)

with cte as (
    select  json_agg(
                        json_build_object(
                            'eighta',eighta,
                            'sdvosb',sdvosb,
                            'edwosb',edwosb
                        )
                    ) a1,duns_number dn, contract_number cn
    from contract_vehicle
    group by duns_number, contract_number)
select json_build_object(
        'contract_number'::text, contract_number::text,
        'info'::text, json_agg(
            json_build_object(
                    'category_id'::text, category::text,
                    'socio'::text, a1
                )
        )
    )
from contract_vehicle inner join cte on (duns_number=dn and contract_number = cn)
group by duns_number, contract_number;

Я все еще даю мне два контракта № в двух рядах. Думаю, мне нужно создать дополнительный cte, который будет размещаться внутри того, что вы написали. Думаю, на твоем примере я смогу разобраться.

spitfiredd 31.10.2018 15:39

Я рад, что это помогло. К вашему сведению. Вы можете использовать функцию string_agg() over(), чтобы соответственно объединить две строки.

SABER 31.10.2018 16:10

Другие вопросы по теме