RR6RiiAu

1I have a data structure I am trying to work with in experimenting and doing a proof of concept using the JSON data type in my sql. I am using node.js for my back end. 
2
3Here is my table syntax 
4```
5DROP TABLE if exists user;
6CREATE TABLE user (
7  `id` varchar(128) NOT NULL,
8	`device_id` varchar(128) NOT NULL,
9  `email` varchar(100) DEFAULT NULL,
10	`device_type` enum('','android','ios','web') DEFAULT NULL,
11  `password` varchar(512) DEFAULT NULL,
12  `login_by` enum('manual','facebook','anon') DEFAULT 'anon',
13  `created_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
14  `modified_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
15  `data` json NOT NULL,
16  PRIMARY KEY (`id`),
17  UNIQUE KEY `device_id` (`device_id`),
18  UNIQUE KEY `email` (`email`)
19) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
20
21```
22
23This what I want my JSON data object to look like
24```
25{
26  "name": "Allen",
27  "albums": []
28}
29```
30### Issue 1. 
31When 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
32```
33insert into user (id, device_id, device_type, data ) values ("39c48d40-de07-11e8-9d18-518c0c9d3ee2","this4","ios",'{"name": "allen", "albums": []}')
34```
35This works however creates a string in the JSON data type. I thought the data type was supposed to be binary
36If I do a `select * from user` then my results will return the JSON object as a string I need to parse. 
37
38If I try to put in straight JSON Object like this I get a SQL error. 
39```
40insert into user (id, device_id, device_type, data ) values ("39c48d40-de07-11e8-9d18-518c0c9d3ee9","this4","ios",{"name": "allen", "likes": []})
41```
42**Question:**
43
44How do I create the JSON data type that is binary and not a serialized string. 
45
46### Issue 2
47This is related to issue one. 
48I am hoping to add an object to the array for albums
49This query worked but with issues 
50```
51UPDATE user SET data = JSON_ARRAY_APPEND( `data` , '$.albums' , '{"id":"sd123asdwe","title":"OU812"}' ) WHERE id = "39c48d40-de07-11e8-9d18-518c0c9d3ee9"
52```
53
54It 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.. 
55There has to be a better way. 
56
57**Question 1.** 
58
59What is the syntax to insert a native JSON object into the array.
60
61After I added several ablums I wanted a function to remove one of them. I was able to successfully remove using 
62```
63UPDATE user SET data = JSON_REMOVE( `data` , '$.albums[0]' ) WHERE id = "39c48d40-de07-11e8-9d18-518c0c9d3ee9"
64```
65**Question 2.** 
66
67What would the query look like if albums was an object and I wanted to add an object to it, for example
68
69```
70{
71  "OU812": {
72    "title": "OU812",
73    "artist": "Van Halen"
74  }
75}
76```
77
78**Question 3**
79
80If I add to the object as in question 2, how would I delete a specific key?
81
82I will have more questions. I am looking for specific code examples based on what I have put here