CODEWITHSUNDEEP
mysqljsonselectjsonpathmysql-json
automatix
automatix

Reputation: 14552

How to SELECT FROM a given level of a JSON column in MySQL?

I got a JSON column (users.roles) with a structure like

{
    "tenant-foo":
    [
        "role-aaa",
        "role-bbb"
    ],
    "tenant-bar":
    [
        "role-bbb",
        "role-ccc"
    ],
    "tenant-buz":
    [
        "role-aaa",
        "role-ccc"
    ],
    ...
    "tenant-...": {
        "role-...",
        ...
    }

}

(The semantic behind it: User roles in a multy-tenant system, where a user can be assigned to multiple companies / tenants and get differet roles in every one of them.)

Now I need only the rows, where one of the roles is e.g. role-aaa.

Is it possible / How to get the rows with a given value (here: role-aaa) on the given level (here: the second one) only with SELECT and JSON_* functions in MySQL?

Upvotes: 0

Views: 50

Answers (0)

Related Questions