#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2019
    Posts
    2
    Rep Power
    0

    Mysql json_insert


    Hi all,

    I am desperate !!

    I am trying to perform functions on a JSON column and it always inserts \ to escape the “ and it adds a “ at the start.

    For instance:

    UPDATE table SET colName = JSON_INSERT(‘colName’, ‘$.Key.Key1’, ‘{“Name”: “Dave”} WHERE ‘username’ = “test”;

    Inserts in the right place, but it inserts this :

    “Key1”: “{\”Name\”: \”Dave\”}”

    I need it to be:

    “Key1”: {”Name”: ”Dave”}

    I know it’s treating it as a String and escaping but I can’t get the outcome I need.

    I’ve tried everything !!

    I don’t want it to escape or put the “ before and after the {}.

    Any help massively appreciated.

    Thanks
  2. #2
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,977
    Rep Power
    9647
    '{“Name”: “Dave”}' is a string value. You told it to insert a string.

    As shown in the documentation for JSON_INSERT, if you want to insert a JSON value then you need to CAST() your string to that type.

    Or, in this case, you could insert the string value "Dave" at the $.Key.Key1.Name location.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2019
    Posts
    2
    Rep Power
    0
    Requinix, you are a lifesaver! Thank you.

    Completely missed that in the documentation as I was reading it on my mobile and didn't scroll across to see the whole text.

    I've added CAST('{“Name”: “Dave”}' AS JSON)) and it works a treat.

    Many thanks, simple but took me hours of head scratching!

    Cheers
  6. #4
  7. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,320
    Rep Power
    1503
    Originally Posted by mossthai
    Requinix, you are a lifesaver! Thank you.
    That's why we like to keep him around.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!

IMN logo majestic logo threadwatch logo seochat tools logo