Thanks Martin. What are the performance ramifications of using LIKE queries though? Wouldn't it essentially need to do a full table scan to look for this substring? And if I wanted to search for N tags at a time, it would be N full table scans? On Thu, Nov 9, 2017 at 7:02 AM, martin van dinther < martin.van.dinther@monetdbsolutions.com> wrote:
Yes this is possible. You will need to use: tags LIKE '%foo%' . So include SQL % wildcards.
Note that LIKE matches case sensitive. To match case insensitive use ILIKE.
bash-4.4$ mclient Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.27.10 (unreleased), 'demo' Type \q to quit, \? for a list of available commands auto commit mode: on
sql>CREATE TABLE my_table (tags json NOT NULL); operation successful (11.805ms)
sql>INSERT INTO my_table VALUES ('["foo","bar","baz"]'); 1 affected row (2.805ms)
sql>SELECT tags FROM my_table; +---------------------+ | tags | +=====================+ | ["foo","bar","baz"] | +---------------------+ 1 tuple (1.944ms)
sql>SELECT tags FROM my_table WHERE tags LIKE '%foo%'; +---------------------+ | tags | +=====================+ | ["foo","bar","baz"] | +---------------------+ 1 tuple (2.271ms)
sql>SELECT count(*) FROM my_table WHERE tags LIKE '%foo%'; +------+ | L3 | +======+ | 1 | +------+ 1 tuple (2.459ms)
sql>SELECT count(*) FROM my_table WHERE tags LIKE '%FOO%'; +------+ | L3 | +======+ | 0 | +------+ 1 tuple (2.265ms)
sql>SELECT count(*) FROM my_table WHERE tags ILIKE '%FOO%'; +------+ | L3 | +======+ | 1 | +------+ 1 tuple (2.437ms)
sql>\q
Regards, Martin
On 06-11-17 17:19, Brian Ploetz wrote:
Hi,
Suppose I have a table with a "tags" column that is of the JSON datatype ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON). The column will contain a JSON array of Strings. For example:
["foo","bar","baz"]
Is it possible to query for rows that contain a specific value in the tags array? For example, if I wanted to find all of the rows where tags contains "foo", my query would look something like:
select count(*) from my_table where tags ???? 'foo';
But it's not clear to me what ???? should be (or if it's even possible) based on the JSON documentation above.
Thanks in advance. BP
_______________________________________________ users-list mailing listusers-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list