MySQL 5.7 JSON data type. 3 part question about proper query formatting. Inserting, updating by adding to an array or object

MySQL 5.7 and the JSON data type, Help with basic 3 basic functions / queries

I have a data structure I am trying to work with in experimenting and doing a proof of concept using the JSON data type in MySQL. I am using node.js for my back end.

Here is my table syntax

DROP TABLE if exists user;
CREATE TABLE user (
  `id` varchar(128) NOT NULL,
	`device_id` varchar(128) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
	`device_type` enum('','android','ios','web') DEFAULT NULL,
  `password` varchar(512) DEFAULT NULL,
  `login_by` enum('manual','facebook','anon') DEFAULT 'anon',
  `created_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data` json NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_id` (`device_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Select allOpen in new window

This what I want my JSON data object to look like

{
  "name": "Allen",
  "albums": []
}

Select allOpen in new window

Issue 1.

When I create a user this is the query being executed. It is formatted as it is by node to actually run. This is based on the documentation I have seen

insert into user (id, device_id, device_type, data ) values ("39c48d40-de07-11e8-9d18-518c0c9d3ee2","this4","ios",'{"name": "allen", "albums": []}')

Select allOpen in new window

This works however creates a string in the JSON data type. I thought the data type was supposed to be binary
If I do a

select * from user 

Select allOpen in new window

then my results will return the JSON object as a string I need to parse.

{
        "id": "39c48d40-de07-11e8-9d18-518c0c9d3ee9",
        "device_id": "this3",
        "email": null,
        "device_type": "ios",
        "password": null,
        "login_by": "anon",
        "created_timestamp": "2018-11-01 14:52:05",
        "modified_timestamp": "2018-11-01 14:52:05",
        "data": "{\"name\": \"Allen\", \"albums\": []}"
    }

Select allOpen in new window

If I try to put in straight JSON Object like this I get a SQL error.

insert into user (id, device_id, device_type, data ) values ("39c48d40-de07-11e8-9d18-518c0c9d3ee9","this4","ios",{"name": "allen", "likes": []})

Select allOpen in new window

Question
How do I create the JSON data type that is binary and not a serialized string.

Issue 2

This is related to issue one.
I am hoping to add an object to the array for albums
This query worked but with issues

UPDATE user SET data = JSON_ARRAY_APPEND( `data` , '$.albums' , '{"id":"sd123asdwe","title":"OU812"}' ) WHERE id = "39c48d40-de07-11e8-9d18-518c0c9d3ee9"

Select allOpen in new window

It added an additional serialized string to the overall object, so now when I get my data I need to parse the json, then loop through the array and parse the individual items..

{
        "id": "5c4c5490-dddc-11e8-b639-2fb5d5a2214b",
        "device_id": "this2",
        "email": null,
        "device_type": "ios",
        "password": null,
        "login_by": "anon",
        "created_timestamp": "2018-11-01 09:45:14",
        "modified_timestamp": "2018-11-01 09:45:14",
        "data": "{\"likes\": [\"{\\\"id\\\":\\\"sd123asdwe\\\",\\\"title\\\":\\\"OU812\\\"}\"]}"
    }

Select allOpen in new window

There has to be a better way.

Question 1.
What is the syntax to insert a native JSON object into the array? I want to avoid having to loop to deserialize.

After I added several albums I wanted a function to remove one of them. I was able to successfully remove using

UPDATE user SET data = JSON_REMOVE( `data` , '$.albums[0]' ) WHERE id = "39c48d40-de07-11e8-9d18-518c0c9d3ee9"

Select allOpen in new window

Question 2.

What would the query look like if albums was an object and I wanted to add an object to it, for example

{
  "OU812": {
    "title": "OU812",
    "artist": "Van Halen"
  }
}

Select allOpen in new window

Question 3

If I add to the object as in question 2, how would I delete a specific key?

I will have more questions. I am looking for specific code examples based on what I have put here