Skip to content

Instantly share code, notes, and snippets.

@zmts
Last active October 25, 2021 17:16
Show Gist options
  • Save zmts/a1fa3fbac07146e383d8b3198b2c76e9 to your computer and use it in GitHub Desktop.
Save zmts/a1fa3fbac07146e383d8b3198b2c76e9 to your computer and use it in GitHub Desktop.
Insert(push) object to nested array. JSONB PostgreSQL

Insert(push) object to nested array. JSONB PostgreSQL

// posts table

| id | content          |
|----|------------------|
| 40 | jsonb data       |
// content field data
{
  "blocks": [
    {
      "type": "TYPE_TEST",
      "content": {
        "list": [
          {
            "id": "Ub4iAqhSi1UnRPHI",
            "test": "2600/Ub4iAqhSi1UnRPHI"
          },
          {
            "id": "zuTEwE9f3ZONgzEa",
            "test": "2600/zuTEwE9f3ZONgzEa"
          }
        ]
      }
    }
  ]
}

To push new object {"id":"some new id"} to blocks[0].content.list, run this query:

UPDATE posts
SET content = jsonb_insert(content, '{blocks,0,content,list,1}', '{"id":"some new id"}'::jsonb, true)
WHERE id=40;
const query = `
  UPDATE posts
  SET content = jsonb_insert(content, '{blocks,${blockIndex},content,list,${insertAfterIndex}}', '${JSON.stringify(newImage)}'::jsonb, true)
  WHERE id=${postId};`

Result:

{
  "blocks": [
    {
      "type": "TYPE_TEST",
      "content": {
        "list": [
          {
            "id": "Ub4iAqhSi1UnRPHI",
            "test": "2600/Ub4iAqhSi1UnRPHI"
          },
          {
            "id": "zuTEwE9f3ZONgzEa",
            "test": "2600/zuTEwE9f3ZONgzEa"
          },
          {
            "id":"some new id"
          }
        ]
      }
    }
  ]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment