Sample Header Ad - 728x90

Update all instances of key inside nested JSON array

3 votes
1 answer
1213 views
I'm trying to update a complex JSON document stored in a table in CockroachDB. The CockroachDB query language is mostly Postgres compatible except for the lack of procedural code, so a Postgres answer without user functions or explicit loops will most likely work. The below sample code is also Postgres-compatible. Among other things that make the document complex are that it contains nested arrays and some redundant structures. The table and document are similar to the following:
> show create table foo;
  table_name |                      create_statement
-------------+--------------------------------------------------------------
  foo        | CREATE TABLE public.foo (
                 id INT8 NOT NULL,
                  data JSONB NOT NULL
              );

> insert into foo (id, data)
values(1, '{
    "foo": {
        "bar": {
            "bar": [
                {
                    "fields": [
                        {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "pink"
                        }
                    ]
                },
                {
                    "id": "ALL",
                    "fields": [
                          {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "white"
                        }
                    ]
                },
                {
                    "id": "1",
                    "fields": [
                          {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "green"
                        }
                    ]
                },
                {
                    "id": "ALL",
                    "fields": [
                                            {
                            "things": [],
                            "key": "something",
                            "value": ""
                        },
                        {
                            "things": [],
                            "key": "somethingelse",
                            "value": "1"
                        },
                        {
                            "things": [],
                            "key": "color",
                            "value": "red"
                        }
                    ]
                }
            ]
        }
    }
}');

> select jsonb_pretty(data) from foo;
                     jsonb_pretty
-------------------------------------------------------
  {
      "foo": {
          "bar": {
              "bar": [
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "pink"
                          }
                      ]
                  },
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "white"
                          }
                      ],
                      "id": "ALL"
                  },
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "green"
                          }
                      ],
                      "id": "1"
                  },
                  {
                      "fields": [
                          {
                              "key": "something",
                              "things": [],
                              "value": ""
                          },
                          {
                              "key": "somethingelse",
                              "things": [],
                              "value": "1"
                          },
                          {
                              "key": "color",
                              "things": [],
                              "value": "red"
                          }
                      ],
                      "id": "ALL"
                  }
              ]
          }
      }
  }
How do I set the value for all instances of the key named color to an empty string ("")? The syntax for handling this on nested arrays eludes me.
Asked by JRL (31 rep)
Jun 29, 2022, 09:01 PM
Last activity: Jun 30, 2022, 07:05 PM