JSON in MySQL, returning null results

Monica Published at Mysql

Monica :

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?

GMB :

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.