Build json_replace query

I need to build JSON_REPLACE query like this:

Code:

UPDATE table_name
SET subjectcombination = JSON_REPLACE(subjectcombination, '$.subjectcombination[0].college', 'ABC')
WHERE id = 10;

How do can I build json_replace in CI4?!

thanks.

->where('id', 10)->set('subjectcombination', "JSON_REPLACE(subjectcombination, '$.subjectcombination[0].college', 'ABC')", false)

@iRedds : Thanks for your reply.

My Json is:

Code:

{
    "socialnetwork": {
        "twitter": "tw-u"
    },
    "information": {
        "country": "Cuba",
        "state": "Sancti Spiritus"
    },
    "settings": {
        "social-enable": "1"
    }
}

And I test with your approach:

Code:

        $builder = $this->db->table('users');
        $builder->where('id', 2)->set('information', "JSON_REPLACE(information, '$.settings[0].social-enable', '0')", false) ;
        $builder->update();

In action I see this error:

Code:

Invalid JSON path expression. The error is around character position 27.

$.settings[0].social-enable = {"settings" : [ { "social-enable" : 1} ]}

$.settings[0].social-enable = {"settings" : [ { "social-enable" : 1} ]}

@iRedds : I change to this but I see same error:

Code:

{
    "socialnetwork": [{
        "twitter": "tw-u"
    }],
    "information": [{
        "country": "Cuba",
        "state": "Sancti Spiritus"
    }],
    "settings": [{
        "social-enable": "1"
    }]
}

@alakian Why don't you check the SQL that the Query Builder create?
Or why don't you use $db->query() ?
https://codeigniter4.github.io/userguide...ar-queries

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

@kenjis : I think my problem not related to the query method. I'v created my query use $db->query() like this:

Code:

$this->db->query('UPDATE users SET information = JSON_REPLACE(information, "$.settings[0].social-enable", "0")');

In action I saw same error!!

I've solved my problem use this:

Code:

$builder->where('id', $id)->set('information', "JSON_REPLACE(information, '$.settings.\"social-enable\"', $data)", false);