postgresql 12 jsonb_path_query how to select some keys to construct a small object from a big jsonb object?

Question

i have a data JSONB column in Postgres 12, which saved a big object with a .interactions field which is an array of big objects, each object is pretty big, having too many fields which are not needed by app layer, wonder is there a way to query out only the interested fields?

{
  "interactions": [
    { "k1": ..., "k2": ..., "k3": ..., "k4": ... },
    { "k1": ..., "k2": ..., "k3": ..., "k4": ... },
    { "k1": ..., "k2": ..., "k3": ..., "k4": ... },

  ]
  // many other key-value pairs
}

Want something like this: suppose if the only interested fields are k1, k3, ...

SELECT jsonb_path_query_array(data, '$.interactions[*].{k1,k3}')

to get slim result objects like:

[ { "k1", ..., "k3": ... }, { "k1", ..., "k3": ... } ]

On command line with jq would be as simple as { k1,k3 }:

➸  echo '{"k1":3,"k2":4,"k3":5,"k4":"ok"}
{"k1":-1,"k2":2,"k3":6}' | jq -c '{ k1, k3 }'
{"k1":3,"k3":5}
{"k1":-1,"k3":6}

One further question: can the interested field names like k1,k3 be not hard-coded but as parameters provided by different application logic?

score 0 · Answer 1 · answered Mar 28 '21 at 04:13

so far I only know how to build a single object, from a big object to a slim one with only interested fields,

SELECT jsonb_build_object(
    'k1', data->'k1',
    'k3', data->'k3',
    // ...
) FROM ...

but not sure how can apply for each object from an array? something like this in javascript:

dataArray.map(obj =>
  ['f1', 'f3'].reduce((acc, k) => Object.assign(acc, {[k]: obj[k]}), {}))

postgresql 12 jsonb_path_query how to select some keys to construct a small object from a big jsonb object?

1 Answers1