postgresql - Concatenate 2 json records (from 2 different select statements)

Question

I would like to concatenate 2 jsons like this : json1,json2.

Each json is a result of a select statement.

Select 1 (json1):

select to_json(PROFITCENTERID) as table1
from (  
    select
        'PROFITCENTERID' as name,
        17 as type,
        '' as value,
        jsonb_build_array(
            jsonb_build_object('name', 'PRCTR_NAME', 'value', PRCTR_NAME, 'type', 0, 'children', '[]'::jsonb),
            jsonb_build_object('name', 'LONG_TEXT', 'value', LONG_TEXT, 'type', 0, 'children', '[]'::jsonb)
        ) as children
    from (
select 'TESTCOSTIN' as PRCTR_NAME, 'TESTCOSTIN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
'1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001118' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
UNION ALL
select 'TESTLUCIAN' as PRCTR_NAME, 'TESTLUCIAN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
'1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001119' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
) c
) PROFITCENTERID

Select 2(json2):

select to_json(SALES) as table2
from (  
    select
        'PROFITCENTERID' as name,
        17 as type,
        '' as value,
        jsonb_build_array(
            jsonb_build_object('name', 'PROFIT_CTR', 'value', PROFIT_CTR, 'type', 0, 'children', '[]'::jsonb),
            jsonb_build_object('name', 'PRCTR_HIER_GRP', 'value', PRCTR_HIER_GRP, 'type', 0, 'children', '[]'::jsonb)
        ) as children
    from (
select 'TESTCOSTIN' as PRCTR_NAME, 'TESTCOSTIN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
'1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001118' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
UNION ALL
select 'TESTLUCIAN' as PRCTR_NAME, 'TESTLUCIAN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
'1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001119' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
) c
) SALES

Is there any way to do this since joining them is not an option?

I have tried using || but I receive the error 'more than one row returned by a subquery used as an expression'

Thank you!

Both queries produce two jsons each, not a single one. Do you want to merge them all into a json array? — klin
– klin, Commented Mar 16, 2019 at 22:08
You are right, my bad @klin, I want to combine the first json from the first select with the first json from the second one and so on. — py me
– py me, Commented Mar 16, 2019 at 22:13
The order of rows is not determined. You probably need a column which has the same value in related rows of the queries to be able to join them together. — klin
– klin, Commented Mar 16, 2019 at 22:40

Richard Wheeldon · Accepted Answer · 2019-03-16 22:11:47Z

If you aggregate the JSONs to create strings first you can then concatenate them. Since the concatenated string won't be valid JSON though, I'm not sure why'd you want to do this and you may want to take a look at array_agg instead.

select (
    select string_agg(to_json(SALES)::text, '') as table2
    from (  
        select
            'PROFITCENTERID' as name,
            17 as type,
            '' as value,
            jsonb_build_array(
                jsonb_build_object('name', 'PROFIT_CTR', 'value', PROFIT_CTR, 'type', 0, 'children', '[]'::jsonb),
                jsonb_build_object('name', 'PRCTR_HIER_GRP', 'value', PRCTR_HIER_GRP, 'type', 0, 'children', '[]'::jsonb)
            ) as children
        from (
    select 'TESTCOSTIN' as PRCTR_NAME, 'TESTCOSTIN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
    '1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001118' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
    UNION ALL
    select 'TESTLUCIAN' as PRCTR_NAME, 'TESTLUCIAN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
    '1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001119' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
    ) c
    ) SALES
) || (
    select string_agg(to_json(SALES)::text, '') as table2
    from (  
        select
            'PROFITCENTERID' as name,
            17 as type,
            '' as value,
            jsonb_build_array(
                jsonb_build_object('name', 'PROFIT_CTR', 'value', PROFIT_CTR, 'type', 0, 'children', '[]'::jsonb),
                jsonb_build_object('name', 'PRCTR_HIER_GRP', 'value', PRCTR_HIER_GRP, 'type', 0, 'children', '[]'::jsonb)
            ) as children
        from (
    select 'TESTCOSTIN' as PRCTR_NAME, 'TESTCOSTIN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
    '1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001118' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
    UNION ALL
    select 'TESTLUCIAN' as PRCTR_NAME, 'TESTLUCIAN' as LONG_TEXT, 'ALICE' as IN_CHARGE, '11' as PRCTR_HIER_GRP,
    '1000_A' as SEGMENT, 'E' as LANGU, 'E' as LANGU2,  'DE02' as CO_AREA, '0000001119' as PROFIT_CTR, '19901010' as VALIDFROM, '20201010' AS VALIDTO
    ) c
    ) SALES
);

Collectives™ on Stack Overflow

postgresql - Concatenate 2 json records (from 2 different select statements)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related