'Add element to sub element of JSON object

I have a table named ext_conf having one of the columns named data of dataType jsonb in it. The content of the field looks something like this:

{
"name": "main_profile_name",
"skipOption": {
    "name": "instance",
    "value": 1
},
"description": "main_profile",
"integration": false,
"integrationType": {
    "name": "download",
    "typeValue": "EXPORT"
},
"processParameters": [
    {
        "name": "SrcFile",
        "defaultVal": "Time*",
        "userPrompt": true,
        "paramType": {
            "name": "text"
        },
        "templateParameter": "SrcFile"
    },
    {
        "name": "directory",
        "defaultValue": "/back",
        "userPrompt": false,
        "paramType": {
            "name": "Text"
        },
        "templateParameter": "directory"
    }
],
"integrationProcess": {
    "name": "My-v1"
},
"allowMinuteInterval": true,
"connectionDefinition": {
    "name": "cloud"
}
}

I need to update processParameters and add a few more elements to it so that it looks like:

{
"name": "main_profile_name",
"skipOption": {
    "name": "instance",
    "value": 1
},
"description": "main_profile",
"integration": false,
"integrationType": {
    "name": "download",
    "typeValue": "EXPORT"
},
"processParameters": [
    {
        "name": "SrcFile",
        "defaultVal": "Time*",
        "userPrompt": true,
        "paramType": {
            "name": "text"
        },
        "templateParameter": "SrcFile"
    },
    {
        "name": "directory",
        "defaultValue": "/back",
        "userPrompt": false,
        "paramType": {
            "name": "Text"
        },
        "templateParameter": "directory"
    },
    {
        "name": "MovedProcessed",
        "defaultValue": "/temp",
        "userPrompted": true,
        "parameterType": {
            "name": "Text"
        },
        "templateParameter": "MovedProcessed"
    },
    {
        "name": "Root",
        "userPrompted": false,
        "parameterType": {
            "name": "Text"
        },
        "templateParameter": "Root"
    }
],
"intProcess": {
    "name": "My-v1"
},
"interval": true,
"connDef": {
    "name": "cloud"
}
}

I am new to it and trying to write SQL but fail to do so, Is there a way to do this in Postgres?

sqljsonpostgresqlsql-update


Solution 1:[1]

You can use jsonb_set to update the value in existing column. Here is the syntax:

UPDATE <table_name> SET <column_name> = JSONB_SET(<column_name>, '{processParameters}', '<new values to be added>'

Check the official doc here.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sharif

Related Questions