Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

AEM 6.4: SQL query for excluding some nodes

Avatar

Level 7

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?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi,

You can try with not operand

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



Arun Patidar

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

Hi,

You can try with not operand

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



Arun Patidar