Update key name of jsonb array using Postgres

Question

There is a JSON string, where we have engagedPartners array:

{
  "sbData": [],
  "listing": [
    {
      "db": {
        "name": "abcd"
      }
    }
  ],
  "audienceStats": {
    "audienceStatus": 0
  },
  "userStats": {
    "audienceName": "testWithNoCategory-1",
    "audienceStatus": 1,
    "engagedpartners": [
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 165,
        "areaPage": 0,
        "areaPageDescription": "xyz"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 336,
        "areaPage": 1,
        "areaPageDescription": "Promotions"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 1,
        "areaPage": 2,
        "areaPageDescription": "Vehicle Details"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 1,
        "areaPage": 3,
        "areaPageDescription": "Search Results"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 1,
        "areaPage": 4,
        "areaPageDescription": "About Us"
      }
    ]
  }
}

Inside it we need to update the key "areaPage" to "productPage" through this inner array using postgres, and the expected output should be shown below:

{
  "sbData": [],
  "listing": [
    {
      "db": {
        "name": "abcd"
      }
    }
  ],
  "audienceStats": {
    "audienceStatus": 0
  },
  "userStats": {
    "audienceName": "testWithNoCategory-1",
    "audienceStatus": 1,
    "engagedpartners": [
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 165,
        "productPage": 0,
        "productPageDescription": "xyz"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 336,
        "productPage": 1,
        "productPageDescription": "Promotions"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 1,
        "productPage": 2,
        "productPageDescription": "Vehicle Details"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 1,
        "productPage": 3,
        "productPageDescription": "Search Results"
      },
      {
        "name": "partner samWebsite 19999",
        "viewsCount": 1,
        "productPage": 4,
        "productPageDescription": "About Us"
      }
    ]
  }
}

I tried various options to reach out to this inner array and then update it but couldn't yet work.

See if this SO [answer](https://stackoverflow.com/a/23500670/14973743) helps. — Anand Sowmithiran, Sep 16 '22 at 07:59

score 0 · Answer 1 · answered Sep 16 '22 at 16:34

1st way. if your JSON data is not stored on rows in the table or is not large in count then you can cast your JSON to text and after then replace text and after then you can again cast to JSON. This is one of the ways. But for many JSON data this way is not recommended.

2nd way. If you have many data, please write me I will write for you query for replacing this using JSON and JSON ARRAY functions on PostgreSQL.

Example about 1st way:

select replace(replace(jsondata::text, 'areaPage', 'productPage'), 'areaPageDescription', 'productPageDescription')::jsonb from your_table

Update key name of jsonb array using Postgres

1 Answers1