Menu
I have a very special case where users can store their own JSON object for records. The reason is that every user has special objects and data structure which is part of his normal record.
I want to let users search for objects in the MySQL DB through my API. I am using the JSON_EXTRACT(json_field, "$ ...") ... syntax or in short the -> syntax.
Users should be able to make queries like this:
get /service/resource?a>3 AND b.c="dog" OR b.d IS NOT NULL
where all parameters (a, b) search on the json field.
First I thought of using a list of parameters, but how should I be able to get the AND, OR, IS NOT NULL operator combining them?
How would you design such a search function for a RESTful API?
714
We can use the JSON_EXTRACT function to extract data from a JSON field. The basic syntax is: JSON_EXTRACT(json_doc, path) For a JSON array, the path is specified with $[index] , where the index starts from 0: mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]'); +------------------------------------------+
-- Stored procedure to insert post and tags DROP PROCEDURE IF EXISTS insert_post; DELIMITER $$ CREATE PROCEDURE insert_post( IN my_data JSON ) BEGIN -- Declare iterator variable to use it later on in the loop DECLARE i INT DEFAULT 0; -- Retrieve values from JSON SET @title = JSON_UNQUOTE(JSON_EXTRACT(my_data, '$.
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.
Google is using "+" for building their queries. Google query "a AND B or NOT c" -> google.com/q=a+AND+b+or+NOT+c
I will try this approuch.
150
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
