Is there a way to merge JSON objects array with another JSON objects array from other some other view/table?

Question

So there are these 2 tables I want to group together by age so that i can find the total number of people from that age group from both the tables so far i'm able to achieve it but the way i want the JSON data to be is wrong

This is my approach:

SELECT temp.age, sum(temp.total), json_agg(temp.data_recs) as data_recs FROM (

        SELECT  
            '31-40' as age, COUNT(*) as total, 
            json_agg(
                  CASE
                  WHEN table_1.age BETWEEN 31 AND 40 
                  THEN json_build_object(
               'country', table_1.country,
               'Name', table_1.name
            )
            ELSE json_build_object(
                  'country', '',
                  'Name', ''
                )
                END
                  ) AS data_recs 
            from table_1 
        WHERE table_1.age BETWEEN 31 AND 40

        UNION ALL

        SELECT  
            '31-40' as age, COUNT(*) as total, 
            json_agg(
                  CASE
                  WHEN table_2.age BETWEEN 31 AND 40
                  THEN json_build_object(
               'country', table_2.country,
               'Name', table_2.name
            )
            ELSE json_build_object(
                  'country', '',
                  'Name', ''
                )
                END
                  ) AS data_recs 
            from table_2 
        WHERE table_2.age BETWEEN 31 AND 40

  ) AS temp group by temp.age

result from 1'st table eg.

  age  |  total |  data_recs
'31-40'|   2    | [{"name":"John","country":"USA"},{"name":"Eddy","country":"USA"}]

result from 2'nd table eg.

  age  |   total |  data_recs
'31-40'|   2     | [{"name":"Cienna","country":"Italy"},{"name":"Rosie","country":"Italy"}]

So far I'm able to achieve

  age  |   total |  data_recs
'31-40'|   4     | [[{"name":"John","country":"USA"},{"name":"Eddy","country":"USA"}],[{"name":"Cienna","country":"Italy"},{"name":"Rosie","country":"Italy"}]]

JSON array 1 = [{"name":"John","country":"USA"},{"name":"Eddy","country":"USA"}]

JSON array 2 = [{"name":"Cienna","country":"Italy"},{"name":"Rosie","country":"Italy"}]

What I want is :-

     age  |   total |  data_recs
  '31-40' |   4     | [{"name":"John","country":"USA"},{"name":"Eddy","country":"USA"},{"name":"Cienna","country":"Italy"},{"name":"Rosie","country":"Italy"}]

comibined JSON what i want Should be like this:

[{"name":"John","country":"USA"},{"name":"Eddy","country":"USA"},{"name":"Cienna","country":"Italy"},{"name":"Rosie","country":"Italy"}]

Is there a way to do this or any other approach?

S-Man · Accepted Answer · 2019-08-29 07:33:26Z

1

Basically you code is as follows

SELECT
    json_agg(json_objects from UNION)
FROM (
    SELECT
        json_agg(
            json_build_object()
        )
    FROM
        ...

    UNION

    SELECT
        json_agg(
            json_build_object()
        )
    FROM
        ...
)

So first you are creating arrays and afterwards you are aggregating the arrays. But if you would leave out your first json_agg() steps, then the high-level json_agg() would aggregate the JSON objects you are creating with json_object_build().

So, only do one aggregation should be your solution:

SELECT
    json_agg(json_arrays from UNION)
FROM (
    SELECT
        json_build_object()
    FROM
        ...

    UNION

    SELECT
        json_build_object()
    FROM
        ...
)

answered Aug 29, 2019 at 7:33

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

smith Over a year ago

is it possible to get the result without removing json_agg() from the inside and then use some unnest function ?

S-Man Over a year ago

Well, I can't see any way which seems to make sense. Of course, you can use your inner json_agg() and unnest the elements afterwards using json_array_elements(). But why should you do this: aggregating only to inverse this operation right after... This could be the code: dbfiddle.uk/…

Collectives™ on Stack Overflow

Is there a way to merge JSON objects array with another JSON objects array from other some other view/table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related