JSON in MySQL, returning null results

Question

In the department table, I have two fields:

documentid, which is INT
jsondocument which is JSON

I executed the following query:

INSERT INTO department VALUES
(1,'{"department":{
"deptid":"d1",
"deptname":"Marketing",
"deptroom":"Room 7",
"deptphone":["465-8541","465-8542","465-8543"],
"employee":[{
"empid":"e1",
"empname":"Mary Jones",
"empphone":"465-8544",
"empemail":["[email protected]","[email protected]"]},
{
"empid":"e2",
"empname":"Tom Robinson",
"empphone":"465-8545",
"empemail":["[email protected]","[email protected]"]},
{
"empid":"e3",
"empname":"Olivia",
"empphone":"465-8546",
"empemail":["[email protected]","[email protected]"]}
]}} ' );

Now, I am trying to return the deptname and the deptphone using this code:

SELECT 
    documentid,
    jsondocument->'$.deptname' as deptname, 
    jsondocument->'$.deptphone' as deptphone
from department;

However, it's returning null values. Where did I go wrong?

Please show us the result that you expect, as tabular text.

GMB
– GMB

2020-05-19 23:39:26 +00:00
Commented May 19, 2020 at 23:39 — GMB
– GMB, Commented May 19, 2020 at 23:39

GMB · Accepted Answer · 2020-05-19 23:41:24Z

2

Basically, you are missing first-level key department in your json path:

select
    documentid,
    jsondocument->'$.department.deptname' as deptname, 
    jsondocument->'$.department.deptphone' as deptphone
from department;

Demo on DB Fiddle:

documentid | deptname    | deptphone                           
---------: | :---------- | :-----------------------------------
         1 | "Marketing" | ["465-8541", "465-8542", "465-8543"]

Note that this gives you deptphone as a json array - because that's how it is stored in the json document. If you want to unnest the array into several rows, then additional processing is needed (in MySQL 8.0, you would typically use json_table()) - you might want to ask a new question for this.

answered May 19, 2020 at 23:41

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashiq K · Accepted Answer · 2020-05-19 23:46:22Z

0

The deptname and deptphone are inside department, So instead of .deptname, you have to use .department.deptname, same with the .deptphone, it will be .department.deptphone

answered May 19, 2020 at 23:46

Ashiq K

665 bronze badges

Collectives™ on Stack Overflow

JSON in MySQL, returning null results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related