Question 243829 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL: varchar json field, find key with value

So inside my table, I have a column named "data" that is varchar(3000).

This is basically a json blob, and this is example of what it holds:

{"homeWidgetToShow":"chat_and_notes","official":false,"nationalFederation":false,"categories":[],"activities":{"0":2,"2":7,"1":3},"clubsBlackList":[],"privateMembers":false,"alwaysShowDescription":false,"restrictMembersFromPositingForums":false}

Now I need to make a query that basically finds if inside data.activities there is a key that has value 3.

In this keys, we see there is a key "1" that has the value 3, so it should be matched.

Caveat is, activities key sometimes may not be set, also it is not always on the key with value "1".

Tags:

3

Answers


  1. 0 
    SELECT JSON_CONTAINS(
  JSON_EXTRACT('{"activities":{"0":2,"2":7,"1":3}}', '$.activities.*'),
  '2','$');

    Replace '{"activities":{"0":2,"2":7,"1":3}}' with target string or column, '2' with search keyword. This will return null when $.activities is not present. Will return 1 when 2 is found in the desired path, 0 when 2 is not found.

    JSON_EXTRACT(doc:json, path:char*) extracts elements specified in path from doc, you can use wildcard * to match any key. After applying extract, it returns text [2,3,7] (seems like ordered), you can then use it as a json array.

    JSON_CONTAINS(doc:json, val:any[, path:varchar]) returns 1 when candidate is found in any paths inside target, returns 0 when non is found, or null when any argument is null.

    Login or Signup to reply.
  2. 0 
    SELECT json, JSON_UNQUOTE(JSON_EXTRACT(json,"$.activities.1")) AS desiredValue FROM `table_name`;

    Note – If activities is not set in JSON, it will return NULL.

    JSON_UNQUOTE is used to remove the double quotes around the extracted values.

    JSON_EXTRACT(data, '$.activities.*') is used to extract all values within the activities object, regardless of their keys.

    [2, 7, 3]

    JSON_EXTRACT(data, '$.activities.1') is used to extract value at index 1 within the activities object.

    3

    If you want to achieve using the Doctrine’s QueryBuilder

    $qb->where($queryBuilder->expr()->eq(
        "JSON_UNQUOTE(JSON_EXTRACT(tableName.data, '$.activities.1'))",
        "3"
))

$qb->where($queryBuilder->expr()->like(
       "tableName.data", // partial search
        $queryBuilder->expr()->literal("%"$1":"$3"%")
))
    Login or Signup to reply.
  3. 0

    You can use the MEMBER OF JSON operator in MySQL 8 to determine if 3 is one of the values in the activities element of the JSON:

    SELECT *
FROM your_table
WHERE 3 MEMBER OF(data->>'$.activities.*')

    Note if activities is not present, JSON_EXTRACT will return NULL and so will MEMBER OF in that case.

    Demo on dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search