Question 89163 in PostgreSQL Question posted in
The official documentation can be found here.

How to MATCH within a list from vertices using Cypher queries? – Postgresql

I was attempting to match and return all vertices that contain a certain tag from within a list.

SELECT * FROM cypher('muse', $$
CREATE(r: jazz {
name: 'Kind of Blue',
artist: 'Miles Davis',
date: '17 August 1959',
sub_genre: ['Modal Jazz', 'Cool Jazz'],
tags: ['instrumental', 'mellow', 'nocturnal', 'soothing', 'improvisation', 'progressive', 'calm'],
rating: 4.31
}) RETURN r
$$) as (jazz_record agtype);

SELECT * from cypher('muse', $$
MATCH (record)
WHERE record.tags = 'mellow'
RETURN record
$$) as (record agtype);

as per the official AGE documentation, there is only mention of using an index or a range.
How would I match all records that contain the tag ‘mellow’ within a list?

Tags:

4

Answers


  1. 0

    You can use the following query:

    SELECT * from cypher('muse', $$
MATCH (record)
WHERE  'mellow' IN record.tags
RETURN record
$$) as (record agtype);

    Here the IN clause checks if ‘mellow’ exists in the tags list.
    Moreover, in the sample query that you have provided, use ‘tags’ property instead of ‘tag’

    Login or Signup to reply.
  2. 0

    As an alternative to Zainab’s answer, you could try the command ANY:

    SELECT * FROM cypher('muse', $$
MATCH (record)
WHERE 'mellow' = ANY(record.tags)
RETURN record
$$) as (record agtype);
    Login or Signup to reply.
  3. 0

    You can also try this:

    SELECT * FROM cypher('muse', $$
MATCH (record)
WHERE ANY(tag IN record.tags WHERE tag = 'mellow')
RETURN record
$$) as (record agtype);
    Login or Signup to reply.
  4. 0

    You can return all vertices that contain a certain tag, within their tags list using the IN operator OR you can use the custom function in your Cypher query.

    Here’s an example query using IN Operator:

    SELECT * FROM cypher('muse', $$
MATCH (record:jazz)
WHERE 'mellow' IN record.tags
RETURN record
$$) as (record agtype);

    Explanation:
    In this query, record:jazz specifies that you are matching nodes labeled ‘jazz’. The WHERE clause filters the results to only include records where ‘mellow’ is present in the record.tags list. The IN operator checks whether ‘mellow’ is present in the list, and the ANY function is implicitly used to iterate over the list. Finally, the RETURN clause returns the matching records.

    Here’s an example query using custom function:
    Firstly, create a function in pg_catalog:

    test=# CREATE OR REPLACE FUNCTION pg_catalog.is_in_list(list agtype, size agtype, what agtype)
    RETURNS boolean AS $$
    BEGIN
      FOR i IN 0..size-1 LOOP
         IF list->i = what
         THEN
            RETURN TRUE;
         END IF;
      END LOOP;
    RETURN FALSE;
    END;
    $$ LANGUAGE plpgsql;

    Then, you can call this function to achieve your desired results.

    SELECT * FROM cypher('muse', $$
MATCH (record:jazz)
WHERE pg_catalog.is_in_list(record.tags, size(record.tags), 'mellow')
RETURN record
$$) as (record agtype);

    Explanation:
    In this query, custom function is used to iterates over each tag in record.tags and checks whether it matches the string ‘mellow’. If at least one tag matches, the function will return true and WHERE clause will become true.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search