mysql - How create VIEW (select) from JSON

Question

everyone.

Please advise how this can be arranged:

1 - I have:

CREATE TABLE table_name (
    id int NOT NULL AUTO_INCREMENT,
    json varchar(5555),
    PRIMARY KEY (id)
);

INSERT INTO table_name (id, json)
VALUES 
  (NULL, '{
    "_id": "6011a8810ae65f75b78fe2fa",
    "index": 0,
    "guid": "5f456074-976b-449e-8061-f806d7703cbe",
    "isActive": true,
    "balance": "$2,434.59",
    "picture": "http://placehold.it/32x32",
    "age": 21,
    "eyeColor": "brown",
    "name": "Hoover England",
    "latitude": -40.914181,
    "longitude": 123.509716,
    "tags": [
      "labore",
      "officia",
      "cillum",
      "enim",
      "sit",
      "irure",
      "dolor"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Medina Stephens"
      },
      {
        "id": 1,
        "name": "Houston Heath"
      },
      {
        "id": 2,
        "name": "Peck Stone"
      }
    ],
    "greeting": "Hello, Hoover England! You have 5 unread messages.",
    "favoriteFruit": "strawberry"
  }'),
  (NULL, '{
    "_id": "6011a88139002e34bc361597",
    "index": 1,
    "guid": "47b3f1cf-25f2-43c1-8fb3-740dca02c65e"}"),
  (NULL, "{"friends": [
      {
        "id": 0,
        "name": "Franco Lara"
      },
      {
        "id": 1,
        "name": "Strickland Love"
      },
      {
        "id": 2,
        "name": "Howe Malone"
      }
    ]}'); 

http://sqlfiddle.com/#!9/533b1a/5

2 - I need this output table view:

+----+---------------+--------------------+----------------------+----------------------------------------+
| id | json_owner_id | variable_path      | variable_name        | variable_value                         |
+----+---------------+--------------------+----------------------+----------------------------------------+
|  1 |           856 | "0._id"            | id                   | "601132e8ed41a6bbaa2748b8"             |
|  2 |           856 | "0.index"          | index                | 0                                      |
|  3 |           856 | "0.guid"           | guid                 | "4fcf9b5f-7b9a-4e2c-ba20-4a683dae9360" |
| .. |               |                    |                      |                                        |
| 11 |           856 | "0.tags.0"         | tags                 | "duis"                                 |
| 12 |           856 | "0.tags.1"         | tags                 | "Lorem"                                |
| 13 |           856 | "0.tags.2"         | tags                 | "incididunt"                           |
| .. |               |                    |                      |                                        |
| 18 |           856 | "0.friends.0.id"   | 0                    |                                        |
| 19 |           856 | "0.friends.0.name" | "Valenzuela Navarro" |                                        |
| 20 |           856 | "0.friends.1.id"   | 1                    |                                        |
| 21 |           856 | "0.friends.1.name" | "Isabelle Griffin"   |                                        |
| .. |               |                    |                      |                                        |
| 25 |           859 | "1._id"            | id                   |"601132|e8b82f87f2dbd2426b"             |
+----+---------------+--------------------+----------------------+----------------------------------------+

How i can solve this? Thanks you very much for your support, tips a triks.

question from:https://stackoverflow.com/questions/65924927/how-create-view-select-from-json

Answer 1

Waitting for answers