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