MgXAdAkN

1-- input:
2/*
3 [
4  {
5    "id": 1,
6    "name": "item1"
7  },
8  [
9    {
10      "id": 3,
11      "name": "item21"
12    },
13    {
14      "id": 4,
15      "name": "item22"
16    }
17  ],
18  {
19    "id": 2,
20    "name": "item3"
21  }
22]
23 */
24-- output:
25/*
26 [
27  {
28    "id": 1,
29    "name": "item1",
30    "new_prop": "new_val"
31  },
32  [
33    {
34      "id": 3,
35      "name": "item21",
36      "new_prop": "new_val"
37    },
38    {
39      "id": 4,
40      "name": "item22",
41      "new_prop": "new_val"
42    }
43  ],
44  {
45    "id": 2,
46    "name": "item3",
47    "new_prop": "new_val"
48  }
49]
50 */
51create table if not exists playground_table
52(
53    include jsonb,
54    id integer not null
55        constraint playground_table_pk
56            primary key
57);
58alter table playground_table owner to postgres;
59
60delete
61from playground_table
62where id = 1;
63
64insert into playground_table
65values ('[
66  {
67    "id": 1,
68    "name": "item1"
69  },
70  [
71    {
72      "id": 3,
73      "name": "item21"
74    },
75    {
76      "id": 4,
77      "name": "item22"
78    }
79  ],
80  {
81    "id": 2,
82    "name": "item3"
83  }
84]', 1);
85
86update playground_table
87set include = (select jsonb_agg(case
88                                    when (jsonb_typeof(row_item) = 'array')
89                                        then (
90                                        select jsonb_agg(value || '{
91                                          "new_prop": "new_val"
92                                        }'::jsonb)
93                                        from jsonb_array_elements(row_item)
94                                    )
95                                    else row_item || '{
96                                      "new_prop": "new_val"
97                                    }'::jsonb
98                                end)
99               from (
100                        select jsonb_array_elements(include)::jsonb as row_item
101                        from playground_table
102                        where id = 1
103                    ) myt)
104where id = 1;
105
106select * from playground_table;