How to add value to nested jsonb array [duplicate]

Question

I have a jsonb value of approximate structure:

'{
    "a": {
        "result": 10,
        "values": [
           {"date": "2024-11-10", "question": 5},
           {"date": "2024-11-11", "question": 10}
        ]
    }
}'

I need to add another object to the "values" array:

{"date": "2024-11-12", "question": 1}

Expected result:

'{
    "a": {
        "result": 10,
        "values": [        [
           {"date": "2024'-11-10", "question": 5},
           {"date": "2024'-11-11", "question": 10},
           {"date": "2024'-11-12", "question": 1}
        ]
    }
}'

How to achieve this in Postgres?

Please tag the question with the version of PostgreSQL that is being used. JSON functions have evolved and expanded with newer versions. — JohnH
– JohnH, Commented Nov 16, 2024 at 22:04
This is not a duplicate of the suggested prior answer - which asks to add an object to a JSON value stored as text, i.e. that other question is a mess. And John's new answer here is also clearly better than anything offered over there. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 17, 2024 at 4:35

JohnH · Accepted Answer · 2024-11-18 00:38:07Z

There are several ways to achieve the described results. One method is to use JSONB_INSERT to append to the values array.

WITH
  t (test_value) AS (
    VALUES
      ('{
          "a": {
            "result": 10,
            "values": [
              {
                "date": "2024-11-10",
                "question": 5
              },
              {
                "date": "2024-11-11",
                "question": 10
              }
            ]
          }
        }'::JSONB
      )
  )
SELECT
  JSONB_INSERT(
    t.test_value,
    '{a, values, -1}',
    '{"date": "2024-11-12", "question": 1}',
    TRUE
  )
FROM
  t;

The call to JSONB_INSERT is used to find the values array and insert the new JSONB object after the last element (look in Table 9.49. JSON Processing Functions for a description of JSONB_INSERT).

The result is:

{
  "a": {
    "result": 10,
    "values": [
      {
        "date": "2024-11-10",
        "question": 5
      },
      {
        "date": "2024-11-11",
        "question": 10
      },
      {
        "date": "2024-11-12",
        "question": 1
      }
    ]
  }
}

This approach should work in all currently supported versions of PostgreSQL (tested with v16 and v17).

Collectives™ on Stack Overflow

How to add value to nested jsonb array [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related