Demonstrates how to extract and aggregate attributes in a json data structure

extract-and-aggregate-json-attributes.sql

SELECT id,
       array_to_json(array_remove(array_agg(a), NULL)) as a,
       array_to_json(array_remove(array_agg(b), NULL)) as b,
       array_to_json(array_remove(array_agg(c), NULL)) as c
FROM (
         SELECT jsonb_path_query_first(value, '$[*].id') as id,
                jsonb_path_query(value, '$[*].a') as a,
                jsonb_path_query(value, '$[*].b') as b,
                jsonb_path_query(value, '$[*].c') as c
         FROM (
                  SELECT jsonb_array_elements('[
                    [
                      {
                        "id": "1"
                      },
                      {
                        "a": "a"
                      },
                      {
                        "b": "b"
                      },
                      {
                        "a": "a"
                      },
                      {
                        "b": "b"
                      },
                      {
                        "c": "c"
                      }
                    ],
                    [
                      {
                        "id": "2"
                      },
                      {
                        "a": "a"
                      },
                      {
                        "b": "b"
                      },
                      {
                        "a": "a"
                      },
                      {
                        "b": "b"
                      },
                      {
                        "b": "b"
                      }
                    ]
                  ]'::jsonb) as value
              ) AS objects
     ) AS objects
GROUP BY id;