AEM 6.4: SQL query for excluding some nodes

Magicr

29-05-2019

Hello,

during my work with AEM 6.4 I've faced following situation:

In my Content repository exists a node which has subnodes. In example the node has the path "/content/sites/startnode". Underneath this node exists some children those have following properties:

- childenode A:

  • 'name' with value 'subnode1', type String
  • 'number' with value '1,2', type String[]
  • ...

- childenode B:

  • 'name' with value 'subnode2', type String
  • 'number' with value '2', type String[]

- childnode C:

  • 'name' with value 'subnode3', type String
  • 'number' with value '3,4', type String[]

I wrote two different SQL-queries:

  1. SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/sites/startnode]) and s.[number] <> '2'
  2. SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/sites/startnode]) and not contains (s.number, '2')

The resultset from first query contains childnode A and C. The resultset from second query contains only childnode C. My questions:

1st: Does '<>' compare values and types at same time? 2nd: How can I exclude some childnodes with avoiding the use of contains?

Accepted Solutions (1)

Accepted Solutions (1)

Arun_Patidar

MVP

30-05-2019

Hi,

You can try with not operand

SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE(s, '/content/sites/startnode') and NOT (s.number = '2')

Answers (0)