I have a JSON column type called data with the following value:
{"name": "tester", "email": "[email protected]"}
I can utilize these values via data->name etc.
However, if I try to access an unknown parameter like data->phone, I get an unknown parameter error because that particular parameter does not exist.
How can I query this so that it will default to null if I try to access a parameter that does not exist?
The syntax for extracting a JSON field is not data->phone, it's data->'$.phone'.
See https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-column-path
If you use the syntax correctly, it returns NULL when there is no field found matching your search.
Demo on MySQL 8.0.14:
create table j (data json);
insert into j set data='{"name": "tester", "email": "[email protected]"}';
select data->email from j;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'email from j' at line 1
select data->'$.email' from j;
+----------------------+
| data->'$.email' |
+----------------------+
| "[email protected]" |
+----------------------+
select data->'$.phone' from j;
+-----------------+
| data->'$.phone' |
+-----------------+
| NULL |
+-----------------+
As per my knowledge,the data you have in is stored as object. And -> operator is used to get data from array. U can use the name of the variable in which you have stored the object to get the data.
Like this -
var d ={ "name": "tester", "email": "[email protected]" };
// now to access it
d["name"] will give your desired result
