Menu
Hi I have car listing website and I want to filter multiple values from a JSON column data
My database table looks like this:
id | label | data
---|----------|---------------------------------
1 | "test 1" | {"Year":"2014","Gear":"Auto"}
2 | "test 2" | {"Year":"2010","Gear":"Manual"}
3 | "test 3" | {"Year":"2009","Gear":"None"}
For example I want to filter by Gear, either "Auto" or "Manual":
SELECT * FROM test WHERE
JSON_EXTRACT(data, "$.Year") in (2010,2014) AND
JSON_EXTRACT(data, "$.Gear") in ("Auto","Manual")
The query returns zero results when I add the filter for Gear, but the filter for Year works fine.
420
To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).
MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: Automatic validation of JSON documents stored in JSON columns.
The drawback? If your JSON has multiple fields with the same key, only one of them, the last one, will be retained. The other drawback is that MySQL doesn't support indexing JSON columns, which means that searching through your JSON documents could result in a full table scan.
In MySQL, the JSON_EXTRACT() function returns data from a JSON document. The actual data returned is determined by the path you provide as an argument. You provide the JSON document as the first argument, followed by the path of the data to return.
MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. Here is the SQL query to extract browser name from details column
JSON data in MySQL is treated as its own data type, a JSON string, and can appear in 2 main forms: Key-value object: a single record which consists of multiple named or indexed fields (or keys) paired with values Nested Array / Table: a table built with multiple key-value objects in a hierarchical format What JSON objects look like
How JSON data is stored in MySQL 8.0 JSON data in MySQL is treated as its own data type, a JSON string, and can appear in 2 main forms: Key-value object: a single record which consists of multiple named or indexed fields (or keys) paired with values Nested Array / Table: a table built with multiple key-value objects in a hierarchical format
However, if you want to extract data from a nested JSON array or JSON object, you cannot use chained -> or ->>. You can only use -> and ->> for the top level and need to use JSON_EXTRACT for nested levels. Let’s extract the scores for each student: Cheers! It works as expected. Use $.key to extract the value of a key from a JSON object.
JSON_EXTRACT returns a JSON string, which includes quotation marks. You can either include this in your search:
SELECT * FROM test
WHERE JSON_EXTRACT(data, "$.Year") in (2010,2014)
AND JSON_EXTRACT(data, '$.Gear') IN ('"Manual"','"Auto"');
Or use the JSON_UNQUOTE function:
SELECT * FROM test
WHERE JSON_EXTRACT(data, "$.Year") in (2010,2014)
AND JSON_UNQUOTE(JSON_EXTRACT(data, '$.Gear')) IN ('Manual','Auto');
See fiddle here: https://www.db-fiddle.com/f/fC48mHEM1nuLUZCTP8CLfs/0
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
