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

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 list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list