How to add new property in an object nested in 2 arrays (JSONB postgresql)

You can use jsonb_set() for this, which takes jsonpath assignments as a text[] (array of text values) as

SELECT jsonb_set(

• input_jsonb,

the starting jsonb document

• path_array '{i,j,k[, ...]}'::text[],

the path array, where the series {i, j, k} progresses at each level with either the (string) key or (integer) index (starting at zero)denoting the new key (or index) to populate

• new_jsonb_value,

if adding a key-value pair, you can use something like to_jsonb('new_value_string'::text) here to force things to format correctly

• create_if_not_exists_boolean

if adding new keys/indexes, give this as true so they'll be appended; otherwise you'll be limited to overwriting existing keys

) 

Example

json

{
  "array1": [
    {
      "id": 1,
      "detail": "test"
    }
  ]
}

SQL

SELECT 
    jsonb_set('{"array1": [{"id": 1, "detail": "test"}]}'::jsonb,
    '{array1,0,update}'::TEXT[],
    to_jsonb('new'::text),
    true
) 

Output

{
  "array1": [
    {
      "id": 1,
      "upd": "new",
      "detail": "test"
    }
  ]
}

Note that you can only add 1 nominal level of depth at a time (i.e. either a new key or a new index entry), but you can circumvent this by providing the full depth in the assignment value, or by using jsonb_set() iteratively:

select
    jsonb_set( 
jsonb_set('{"array1": [{"id": 1, "detail": "test"}]}'::jsonb, '{array1,0,upd}'::TEXT[], '[{"new": "val"}]'::jsonb, true), 
    '{array1,0,upd,0,check}'::TEXT[],
    '"test_val"',
    true)

would be required to produce

{
  "array1": [
    {
      "id": 1,
      "upd": [
        {
          "new": "val",
          "check": "test_val"
        }
      ],
      "detail": "test"
    }
  ]
}

If you need other, more complex logic to evaluate which values need to be added to which objects, you can try:

• dynamically creating a set of jsonb_set() statements for execution
• using the outputs from queries of jsonb_each() and jsonb_array_elements() to evaluate the row logic down at the SubEntities level, and then using jsonb_object_agg() and jsonb_agg() as appropriate to build the document back up to the root level from the resultant object-arrays and key-value collections