CODEWITHSUNDEEP
mysqljson
stardust4891
stardust4891

Reputation: 2560

Access MySQL json column parameter as NULL if it is unknown

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?

Upvotes: 0

Views: 759

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562981

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            |
+-----------------+

Upvotes: 1

mujtaba siddiqui
mujtaba siddiqui

Reputation: 31

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

Upvotes: 0

Related Questions