0

I am having trouble understanding how to select attributes from mysql json, my record is like this:

CREATE TABLE configuration (
    name varchar(100) NULL,
    version varchar(100) NULL,
    trees json NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

One insert:

INSERT INTO configuration  VALUES ('test-config', 'version-default', '{
    "trees": [
        {
            "tree_id": "TEST_TREE",
            "site_id": "MSI",
            "nodes": [
                {
                    "node_version": 5,
                    "children_right": 3,
                    "node_position": 1,
                    "type": "root",
                    "node_id": "ROOT_TEST",
                    "children_left": 2
                },
                {
                    "node_version_id": 4,
                    "children_right": null,
                    "node_position": 2,
                    "type": "empty",
                    "node_id": "TEST_LEFT",
                    "children_left": null
                },
                {
                    "node_version_id": 61,
                    "children_right": null,
                    "node_position": 3,
                    "type": "empty",
                    "node_id": "TEST_RIGHT",
                    "children_left": null
                }
            ],
            "version": 1
        }
    ],"status":"PENDING"
}');

Now if for example I want to select the field status from the json I can do the following:

SELECT trees->'$.status' AS status FROM configuration c;

But if I want to select for example the element 0 from the trees json array and then select the tree_id, how could I do this?

I was trying:

SELECT trees->'$.trees.[0].tree_id' AS trees FROM configuration c;

but no luck

And what if I want to go even deeper to the nodes json array?

Thank you in advance!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
BugsOverflow
  • 386
  • 3
  • 19
  • 1
    `.[0]` should not have `.`, just `[0]`. Just lilke array indexing in most other languages. `$.trees[0].tree_id` – Barmar Apr 18 '23 at 21:12
  • 1
    Read the [documentation](https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax) – Barmar Apr 18 '23 at 21:14
  • It's the same for deeper: `$.trees[0].nodes[1].node_id` – Barmar Apr 18 '23 at 21:26
  • Thank you, how could I do a query to get all the records (configurations) where the trees json field, contains a tree inside the trees array which tree_id is called TEST_TREE? I was trying SELECT trees->'$.trees.tree_id' AS trees FROM configuration c where trees->'$.trees.tree_id' = 'TEST_TREE'; – BugsOverflow Apr 18 '23 at 21:48
  • The easiest way is with `JSON_TABLE()` to convert the array to a table. – Barmar Apr 18 '23 at 21:49
  • 1
    See https://stackoverflow.com/questions/38657793/correct-syntax-for-mysql-json-path-to-traverse-arrays for how to use `JSON_SEARCH()` – Barmar Apr 18 '23 at 21:51
  • 1
    In general, nothing using JSON is easy in MySQL. If you have any choice, I recommend you stop using it. – Barmar Apr 18 '23 at 21:51
  • Thanks, it worked like this: select * from configuration where trees->'$.trees[*].tree_id' = JSON_ARRAY('TEST_TREE'); Do not really understand why is the JSON_ARRAY necesary but oh well I guess it is a matter of getting used to it, im gonna be using SpringBoot data jpa to integrate this so I think it will not be to hard – BugsOverflow Apr 18 '23 at 22:32
  • Or should I separate this trees field into another table? it is going to be more time coding it tho – BugsOverflow Apr 18 '23 at 22:32
  • 1
    The proper way to normalize this would be to have a `trees` table with FK to `configuration`, and a `tree_nodes` table with a FK to `trees`. – Barmar Apr 19 '23 at 16:00
  • @Barmar sounds good too, the tree_nodes table would also have a FK to a nodes table right? – BugsOverflow Apr 19 '23 at 22:42
  • 1
    But if you have a many-to-many relationship, you could have `trees`, `nodes`, and then a `tree_nodes` table with FK to both of them. – Barmar Apr 19 '23 at 22:44
  • @Barmar alright sounds good too, would be a OneToMany, one tree can have many nodes, and that way I do not make this too complex, yeah the other way is also good, a Node can also be on many different trees (because reused), will keep in mind both solutions, for now I will keep the json field but if anything goes wrong will have this suggestions in mind to make normalization, ty – BugsOverflow Apr 19 '23 at 22:45

0 Answers0