Retrieving Values of Json Data Inside JSON Array in Mysql

Question

I have JSON column, containing the JSON array. My Scenario, is to get all the the records where value of url is '"example.com/user1"' is present. I have trouble writing the query for this operation.

Record1
 [
    {
        "id": "1",
        "firstname": "user1",
        "url": "example.com/user1"
    },
    {
        "id": "2",
        "firstname": "user2",
        "url": "example.com/user2"
    }
]
Record2
     [
        {
            "id": "1",
            "firstname": "user3",
            "url": "example.com/user3"
        },
        {
            "id": "2",
            "firstname": "user2",
            "url": "example.com/user2"
        }
    ]
......
......
......
Record10
     [
        {
            "id": "1",
            "firstname": "user10",
            "url": "example.com/user10"
        },
        {
            "id": "2",
            "firstname": "user1",
            "url": "example.com/user1"
        }
    ]

The Query Which I ran is:

Select internal_id from users_dummy where JSON_EXTRACT(user_friends, '$[0].url') = "example.com/user1" or JSON_EXTRACT(user_friends, '$[1].url') = "example.com/user1";

So o/p was: Record1, Record10

Is this the proper way to search for the values across the records? Thanks in advance.

Do you mean the JSONString in in a column on your table defined as JSON type? — RiggsFolly
– RiggsFolly, Commented Jan 29, 2020 at 18:27
Provide a fiddle (or CREATE TABLE + INSERT INTO) instead of data example. All fields except JSON field may be skipped. — Akina
– Akina, Commented Jan 29, 2020 at 18:46
@RiggsFolly Yes !! The searchable JSON String is in a JSON column. — Rahul Reddy
– Rahul Reddy, Commented Jan 29, 2020 at 19:18

Sebastian Brosch · Accepted Answer · 2020-01-29 21:26:49Z

1

You can use JSON_SEARCH like this:

SELECT * 
FROM users_dummy 
WHERE JSON_SEARCH(user_friends, 'one', 'example.com/user1', NULL, '$[*].url') IS NOT NULL

demo on dbfiddle.uk

You can use the following solution in case you are using objects instead of arrays:

SELECT * 
FROM users_dummy 
WHERE JSON_SEARCH(user_friends, 'one', 'example.com/user1', NULL, '$.*.url') IS NOT NULL

demo on dbfiddle.uk

edited Jan 29, 2020 at 21:26

answered Jan 29, 2020 at 21:10

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Reddy Over a year ago

Works !! Can you add How can the query change if we change the Record structure into json_object of objects rather than json_array of objects ?? example :

{     "1": {        "firstname": "user10",        "url": "example.com/user10"     },     "2" : {        "firstname": "user1",        "url": "example.com/user1"     } }

Collectives™ on Stack Overflow

Retrieving Values of Json Data Inside JSON Array in Mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related