CODEWITHSUNDEEP
mysqlsqlarraysjson
Gunjal Ray
Gunjal Ray

Reputation: 165

Search in mysql json column to check multiple criteria on same index of array

I'm using MYSql server 8.0.17.

I want to get record with uId= 'UR000001' and also with VIEW = 'Y' from the security column(Shown in Table).

Viewid          Security
VW0000000002    {"security": [{"uId": "UR000001", "edit": "N", "view": "Y"}, {"uId": "UR000002", "edit": "N", "view": "Y"}]}
VW0000000013    {"security": [{"uId": "UR000001", "edit": "N", "view": "N"}, {"uId": "UR000002", "edit": "N", "view": "Y"}]}
VW0000000014    {"security": [{"uId": "UR000001", "edit": "N", "view": "Y"}, {"uId": "UR000002", "edit": "N", "view": "Y"}]}

JSON_SEARCH function searches all array elements of the record that I don't want.

Here is the query that I had tried but it returns the result with all matching (uID='UR000001' OR View='Y')

SELECT viewid, 
       Json_search(`security`, 'one', 'UR000001', NULL, '$.security[*].uId'), 
       Json_search(`security`, 'one', 'Y', NULL, '$.security[*].view') 
FROM   vw_viewmaster 
WHERE  Json_search(`security`, 'one', 'UR000001', NULL, '$.security[*].uId') 
       AND Json_search(`security`, 'one', 'Y', NULL, '$.security[*].view');

Actual Result:(uID='UR000001' OR View='Y')

VW0000000002    "$.security[0].uId" "$.security[0].view"
VW0000000013    "$.security[0].uId" "$.security[1].view"
VW0000000014    "$.security[0].uId" "$.security[0].view"

Expected result:(uID='UR000001' AND View='Y')

VW0000000002    "$.security[0].uId" "$.security[0].view"
VW0000000014    "$.security[0].uId" "$.security[0].view"

Upvotes: 3

Views: 1951

Answers (1)

GMB
GMB

Reputation: 222492

In MySQL 8.0, you can use handy JSON function json_table() to convert a json array to rows. You can then search the resultset.

The following query gives you all viewids whose at least one array element with attribute uId is equal to 'UR000001' and attribute view is 'Y':

select v.viewid
from vw_viewmaster v
where exists (
    select 1
    from json_table(
        v.security -> '$.security',
        '$[*]'
        columns(
            uid  varchar(50) path '$.uId',
            edit varchar(1)  path '$.edit',
            view varchar(1)  path '$.view'
        )
    ) x
    where x.uid = 'UR000001' and x.view = 'Y'
);

For your dataset, this produces:

| viewid       |
| ------------ |
| VW0000000002 |
| VW0000000014 |

If you want the details of the matching array object(s), then:

select v.viewid, x.*
from vw_viewmaster v
cross join json_table(
    v.security -> '$.security',
    '$[*]'
    columns(
        rowid for ordinality,
        uid   varchar(50) path '$.uId',
        edit  varchar(1)  path '$.edit',
        view  varchar(1)  path '$.view'
    )
) x
where x.uid = 'UR000001' and x.view = 'Y'

As a bonus, rowid gives you the index of the matching object in the JSON array (the first object has index 1).

This yields:

| viewid       | rowid | uid      | edit | view |
| ------------ | ----- | -------- | ---- | ---- |
| VW0000000002 | 1     | UR000001 | N    | Y    |
| VW0000000014 | 1     | UR000001 | N    | Y    |

However please note that if more than one object in the array that satisfies the conditions, the above query would generate more than one row per row in the original table (this is why I used exists in the first query).

Upvotes: 5

Related Questions