CodeIgniter Forums
Build json_replace query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Build json_replace query (/showthread.php?tid=81459)

Build json_replace query - alakian - 03-06-2022

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.

RE: Build json_replace query - iRedds - 03-06-2022

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

RE: Build json_replace query - alakian - 03-07-2022

@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.


RE: Build json_replace query - iRedds - 03-07-2022

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

RE: Build json_replace query - alakian - 03-07-2022

(03-06-2022, 02:46 AM)iRedds Wrote: $.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"
    }]
}


RE: Build json_replace query - kenjis - 03-07-2022

@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/database/queries.html#regular-queries

RE: Build json_replace query - alakian - 03-07-2022

@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!!

RE: Build json_replace query - alakian - 03-12-2022

I've solved my problem use this:

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