Question 300943 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Copy value from jsonb column to another nested jsonb column

I have an entity that has, among other things, two jsonb columns : "options" and "features". I want to copy data from a field inside the options column to a two levels deep field inside features.
What I’ve tried :

UPDATE entity 
SET features = jsonb_set(  features
                         , '{informations}'
                         , "features"->'informations' 
                         ||'{"test": "options"->'test'}'
                         , true);

But I get this error when trying to run my migration :

QueryFailedError: syntax error at or near "test"

I think that the simple quotes around the {} and test create the error.

How can I do this ?

FYI : the reverse migration is working perfectly with :

UPDATE entity 
SET options = jsonb_set(  options
                        , '{test}'
                        , features->'informations'->'test'
                        , true);

Because there is no nested simple quotes here.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The solution was :

    UPDATE domain
SET features = jsonb_set(features, '{informations,test}', "options"->'test', true)

  2. 0

    You need only a few of those quotes. You also need to wrap the extractions in parentheses, otherwise they will be evaluated left-to-right, in an unintuitive order: db<>fiddle demo

    features options
    {
        "informations": {
            "features_k121": 121,
            "features_k122": {
                "features_k131": 131
            }
        },
        "features_k111": 11
    }    		 {
        "test": {
            "options_key221": 221
        },
        "options_key211": 211
    }    		 
    UPDATE entity 
SET features = jsonb_set(  features
                         , '{informations}'
                         , (features->'informations')
                         ||(options ->'test')
                         , true)
returning jsonb_pretty(features) as features
         ,jsonb_pretty(options) as options;
    features options
    {
        "informations": {
            "features_k121": 121,
            "features_k122": {
                "features_k131": 131
            },
            "options_key221": 221
        },
        "features_k111": 11
    }    		 {
        "test": {
            "options_key221": 221
        },
        "options_key211": 211
    }

    The unintuitive behaviour I’m referring to is this (shown in the demo):

    features -> 'informations' || options -> 'test'

    Being evaluated like this:

    ((features -> 'informations') || options) -> 'test'

    And not like this

    (features -> 'informations') || (options -> 'test')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search