Styjun

Early arrival in Australia, early hotel check in not available

What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?

Should these notes be played as a chord or one after another?

Pre-1993 comic in which Wolverine's claws were turned to rubber?

Ex-manager wants to stay in touch, I don't want to

How do I tell my supervisor that he is choosing poor replacements for me while I am on maternity leave?

The lexical root of the perfect tense forms differs from the lexical root of the infinitive form

Set a camera to free fall like a Rigid Body?

How could a Lich maintain the appearance of being alive without magic?

semanage not changing file context

Does Lawful Interception of 4G / the proposed 5G provide a back door for hackers as well?

How old is Captain America at the end of "Avengers: Endgame"?

Why does TypeScript pack a Class in an IIFE?

Renting a house to a graduate student in my department

Exception propagation: When to catch exceptions?

Will change of address affect direct deposit?

find not returning expected files

How can a Lich look like a human without magic?

Was there ever any real use for a 6800-based Apple I?

Guns in space with bullets that return?

What does this quote in Small Gods refer to?

Remove everything except csv file Bash Script

Delta TSA-Precheck status removed

How to make a language evolve quickly?

Access MySQL json column parameter as NULL if it is unknown

How do you set a default value for a MySQL Datetime column?How to find all the tables in MySQL with specific column names in them?How do I specify unique constraint for multiple columns in MySQL?MySQL load NULL values from CSV dataSet NOW() as Default Value for datetime datatype?REST API - PUT vs PATCH with real life examplesHow to parse json value of text column in postgresNative JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?Alter an existing mysql column to a JSON data typeSearch in Json column with Laravel

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have a JSON column type called data with the following value:

"name": "tester", "email": "tester@example.com"

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?

mysql json

share|improve this question

asked Mar 23 at 11:05

Kevin DionKevin Dion

11617

add a comment | 

0

I have a JSON column type called data with the following value:

"name": "tester", "email": "tester@example.com"

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?

mysql json

share|improve this question

asked Mar 23 at 11:05

Kevin DionKevin Dion

11617

add a comment | 

I have a JSON column type called data with the following value:

"name": "tester", "email": "tester@example.com"

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?

mysql json

share|improve this question

asked Mar 23 at 11:05

Kevin DionKevin Dion

11617

"name": "tester", "email": "tester@example.com"

share|improve this question

asked Mar 23 at 11:05

Kevin DionKevin Dion

11617

share|improve this question

asked Mar 23 at 11:05

Kevin DionKevin Dion

11617

asked Mar 23 at 11:05

Kevin DionKevin Dion

11617

asked Mar 23 at 11:05

Kevin DionKevin Dion

11617

2 Answers
2

active

oldest

votes

1

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": "tester@example.com"';

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' |
+----------------------+
| "tester@example.com" |
+----------------------+

select data->'$.phone' from j;
+-----------------+
| data->'$.phone' |
+-----------------+
| NULL |
+-----------------+

share|improve this answer

answered Mar 23 at 21:59

Bill KarwinBill Karwin

389k65524682

add a comment | 

0

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": "tester@example.com"
;

// now to access it

d["name"] will give your desired result

share|improve this answer

answered Mar 23 at 11:27

mujtaba siddiquimujtaba siddiqui

115

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This isn't the problem. The problem is attempting to query unknown parameters.
  
  – Kevin Dion
  Mar 23 at 11:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it will be more helpful with code.
  
  – mujtaba siddiqui
  Mar 23 at 11:41
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55313039%2faccess-mysql-json-column-parameter-as-null-if-it-is-unknown%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

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": "tester@example.com"';

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' |
+----------------------+
| "tester@example.com" |
+----------------------+

select data->'$.phone' from j;
+-----------------+
| data->'$.phone' |
+-----------------+
| NULL |
+-----------------+

share|improve this answer

answered Mar 23 at 21:59

Bill KarwinBill Karwin

389k65524682

add a comment | 

1

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": "tester@example.com"';

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' |
+----------------------+
| "tester@example.com" |
+----------------------+

select data->'$.phone' from j;
+-----------------+
| data->'$.phone' |
+-----------------+
| NULL |
+-----------------+

share|improve this answer

answered Mar 23 at 21:59

Bill KarwinBill Karwin

389k65524682

add a comment | 

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": "tester@example.com"';

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' |
+----------------------+
| "tester@example.com" |
+----------------------+

select data->'$.phone' from j;
+-----------------+
| data->'$.phone' |
+-----------------+
| NULL |
+-----------------+

share|improve this answer

answered Mar 23 at 21:59

Bill KarwinBill Karwin

389k65524682

create table j (data json);

insert into j set data='"name": "tester", "email": "tester@example.com"';

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' |
+----------------------+
| "tester@example.com" |
+----------------------+

select data->'$.phone' from j;
+-----------------+
| data->'$.phone' |
+-----------------+
| NULL |
+-----------------+

share|improve this answer

answered Mar 23 at 21:59

Bill KarwinBill Karwin

389k65524682

answered Mar 23 at 21:59

Bill KarwinBill Karwin

389k65524682

answered Mar 23 at 21:59

Bill KarwinBill Karwin

389k65524682

0

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": "tester@example.com"
;

// now to access it

d["name"] will give your desired result

share|improve this answer

answered Mar 23 at 11:27

mujtaba siddiquimujtaba siddiqui

115

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This isn't the problem. The problem is attempting to query unknown parameters.
  
  – Kevin Dion
  Mar 23 at 11:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it will be more helpful with code.
  
  – mujtaba siddiqui
  Mar 23 at 11:41
  

  
  
  
  

add a comment | 

0

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": "tester@example.com"
;

// now to access it

d["name"] will give your desired result

share|improve this answer

answered Mar 23 at 11:27

mujtaba siddiquimujtaba siddiqui

115

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This isn't the problem. The problem is attempting to query unknown parameters.
  
  – Kevin Dion
  Mar 23 at 11:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it will be more helpful with code.
  
  – mujtaba siddiqui
  Mar 23 at 11:41
  

  
  
  
  

add a comment | 

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": "tester@example.com"
;

// now to access it

d["name"] will give your desired result

share|improve this answer

answered Mar 23 at 11:27

mujtaba siddiquimujtaba siddiqui

115

share|improve this answer

answered Mar 23 at 11:27

mujtaba siddiquimujtaba siddiqui

115

answered Mar 23 at 11:27

mujtaba siddiquimujtaba siddiqui

115

answered Mar 23 at 11:27

mujtaba siddiquimujtaba siddiqui

115