Access a JSONB array of objects as an object of arrays

Question

I have a JSONB column in my Postgres 9.6 database with roughly the following structure

[
  {
    "A": "some value",
    "B": "another value",
    "foo": "bar",
    "x": "y"
  },
  {
    "B": "abc",
    "C": "asdf"
  }
]

It is always an array of objects, the number of array elements varies. Some of the object keys are in each array element, but not all of them. The real objects have many more keys, a few hundred are common.

In certain situations, I need to get the value of a specific key for each array element. For example, if I want to access the key "B" the result should be

["another value", "abc"]

if I access "foo" the result should be

["bar", null]

Is there a reasonably efficient way to fetch all values for a specific key in a SQL query? It should work independent of the number of objects in the array, and ideally it should not get slower if the number of key in the objects get much larger.

Answer 1

You can use the jsonb_array_elements to extract each object, aggregate those you want in an array using ARRAY_AGG and then convert that into a json array using array_to_json:

WITH j(json) AS (
  VALUES ('[
    {
      "A": "some value",
      "B": "another value",
      "foo": "bar",
      "x": "y"
    },
    {
      "B": "abc",
      "C": "asdf"
    }
  ]'::jsonb)
)
SELECT array_to_json(ARRAY_AGG(elem->'B'))
FROM j, jsonb_array_elements(json) elem
;
      array_to_json
-------------------------
 ["another value","abc"]
(1 row)

---

WITH j(json) AS (
  VALUES ('[
    {
      "A": "some value",
      "B": "another value",
      "foo": "bar",
      "x": "y"
    },
    {
      "B": "abc",
      "C": "asdf"
    }
  ]'::jsonb)
)
SELECT array_to_json(ARRAY_AGG(elem->'foo'))
FROM j, jsonb_array_elements(json) elem
;
 array_to_json
---------------
 ["bar",null]
(1 row)