How to read JSON key value from JSON object from Database (In Mysql or Sqlite database)?

PHP & Mysql

//My code snippet
SELECT JSON_EXTRACT('{"id":"A0001","name":"shilpa"}', '$.name');

I want to extract key-value from Json stored in my database table.

But above statement should return shilpa, In my case it is returning as “shilpa” (Returning value with double quotes)

On the UI side, I am using angular and I have to remove/replace double quotes with empty strings all the time! I am using Mysql 8.0 and SQLite DB v3.0

Is there a way to ignore double quotes which are appending in JSON_EXTRACT?

Comments

Mohamed Atef

31 Mar 2022

I believe you can remove the qoutes in this way

<?php
$s = '"hello"';
$final = str_replace('"', '', $s);
echo $final;
?>

and you can use foreach to extract the object and rebuild it

you can use foreach only to get the key of the array like

foreach($arr as $key => $var){
 echo $key . " " . $var;
}

Good luck