Add JSON object to an specific key in Postgres

Question

I have a JSON with this structure:

[
   {
      "myKey":[
         {
            "value":1
         },
         {
            "value":2
         }
      ],
      "secondKey":[
         {
            "value":1
         },
         {
            "value":2
         }
      ]
   }
]

And I need to add objects evaluating the key, for example, for myKey add:

[
   {
      "myKey":[
         {
            "value":1
         },
         {
            "value":2
         },
         {
            "new value":3
         }
      ],
      "secondKey":[
         {
            "value":1
         },
         {
            "value":2
         }
      ]
   }
]

What would be the correct way to do it, considering that I'm just using postgres select and procedures.

Stefanov.sm · Accepted Answer · 2021-07-26 18:23:38Z

1

Use jsonb_set with the original myKey array concatenated with {"new value":3}. The CTE t(j) is for demonstration only.

with t(j) as ( values (
'[{
   "myKey":[{"value":1}, {"value":2}], "secondKey":[{"value":1},{"value":2}]
 }]'::jsonb))

SELECT jsonb_set(j, '{0, myKey}', j->0->'myKey' || '{"new value":3}')
FROM t;

Result:

[
  {
    "myKey": [
      {
        "value": 1
      },
      {
        "value": 2
      },
      {
        "new value": 3
      }
    ],
    "secondKey": [
      {
        "value": 1
      },
      {
        "value": 2
      }
    ]
  }
]

You may prefer to use jsonb_build_object('new value', 3) instead of '{"new value":3}'.

edited Jul 26, 2021 at 18:23

answered Jul 26, 2021 at 18:17

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add JSON object to an specific key in Postgres

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related