Transform Postgress JSONB column to another JSONB format

0
votes

Lets say I have a column called words: It looks like this:

{
    "enterprise": [
        {
            "name": "bin",
            "type": 0,
            "props": {"score": 1, "id": "bin"}
        }, 
        {
            "name": "bist",
            "type": 0,
            "props": {"score": 1, "id": "bin"}
        }],
    "private": [
        {
            "name": "hello",
            "type": 0,
            "props": {"score": 1, "id": "hello"}
        },
        {
            "name": "hello",
            "type": 0,
            "props": {"score": 1, "id": "hello"}
        }
    ]
}

It can also be partially empty

{
  "enterprise": [],
  "private": []
}

NEW format should be this:

{
  "private": [
    {
      id: "bin",
      score: 1,
      visible: false, 
      words: ["bin", "bist"],
      other: [], 
      exclude: [] 
    }
  ]
}

I am aware that I would need some reduce sql json function because I am converting from flat structure to nested, but don't know exactly where to look at.

mysqlsqlpostgresql