N1QL query to filter and search for two nested array of objects in the parent objects

SQL++
1

I have a document like this.

{
  "pageName": "testtag1",
  "tags": {
    "primary": [
      {
        "groupName": "visitorSegments",
        "id": "4d044b05-6523-40b4-9fea-ef04567e1f6e",
        "tagName": "HXYZ",
        "tagTitle": "HXYZ"
      },
      {
        "groupName": "visitorSegments",
        "id": "20a38ec7-3724-404a-bed9-d046ae3f60b7",
        "tagName": "HABC",
        "tagTitle": "HABC"
      },
      {
        "groupName": "visitorSegments",
        "id": "4066eac4-e9a1-4fd5-9175-2914644f16c3",
        "tagName": "Int Test 1",
        "tagTitle": "Int Test 1"
      },
      {
        "groupName": "visitorSegments",
        "id": "da8c775e-0968-4fcf-aedd-0b2ad105f9QA",
        "tagName": "name with spaces",
        "tagTitle": "title"
      }
    ],
    "secondary": [
      {
        "groupName": "visitorSegments",
        "id": "48ee51d8-4f64-4a51-a207-84d55995d405",
        "tagName": "TABC",
        "tagTitle": "TABC"
      },
      {
        "groupName": "visitorSegments",
        "id": "842dc7cf-7d7b-4df6-9dff-f4a14af81027",
        "tagName": "Test1111",
        "tagTitle": "Test a - 0"
      },
      {
        "groupName": "visitorSegments",
        "id": "92eca3f0-1192-455b-b2cc-69926d5e7c5c",
        "tagName": "Test a - 1",
        "tagTitle": "Test a - 1"
      },
      {
        "groupName": "visitorSegments",
        "id": "b2a2c298-12ae-4cd4-a94f-b6b846bda583",
        "tagName": "Int Test 2",
        "tagTitle": "Int Test 2"
      }
    ]
  },
  "type": "pqr",
}

And I am UNNESTING two arrays of primary and secondary tags and returning one tagtile value which matches either in primary or secondary matches the string “%test%”.

This is the query i am writing but not getting the desired result.

SELECT DISTINCT x.tagTitle, y.tagTitle as sec
FROM mydb AS doc
UNNEST doc.tags.primaryAS x
UNNEST doc.tags.secondary AS y
WHERE doc.type=‘pqr’
AND (LOWER(x.tagTitle) LIKE “%test%”
OR LOWER(y.tagTitle) LIKE “%test%”)

2

@vsr1 Could you please help on this ?

3

Assume both primary and secondary present. otherwise do ARRAY_CONCAT(IFMISSING(d.tags.primary,), IFMISSING(d.tags.secondary,))

SELECT DISTINCT RAW tag.tagTitle
FROM mydb AS d
UNNEST ARRAY_CONCAT(d.tags.primary, d.tags.secondary) AS tag
WHERE d.type="pqr" AND LOWER(tag.tagTitle) LIKE "%test%";
4

Thanks for the response but when I use this query.

SELECT tag.tagTitle
FROM mydb AS d
UNNEST ARRAY_CONCAT(d.tags.primary, d.tags.secondary) AS tag
WHERE d.type="pqr"

Since there are 8 arrays of objects both in primary and secondary but I am seeing results for 5 arrays of objects and the search is happing from there. Is this something you can please explain @vsr1

5 
WITH mydb AS ({ "pageName": "testtag1", "tags": { "primary": [ { "groupName": "visitorSegments", "id": "4d044b05-6523-40b4-9fea-ef04567e1f6e", "tagName": "HXYZ", "tagTitle": "HXYZ" }, { "groupName": "visitorSegments", "id": "20a38ec7-3724-404a-bed9-d046ae3f60b7", "tagName": "HABC", "tagTitle": "HABC" }, { "groupName": "visitorSegments", "id": "4066eac4-e9a1-4fd5-9175-2914644f16c3", "tagName": "Int Test 1", "tagTitle": "Int Test 1" }, { "groupName": "visitorSegments", "id": "da8c775e-0968-4fcf-aedd-0b2ad105f9QA", "tagName": "name with spaces", "tagTitle": "title" } ], "secondary": [ { "groupName": "visitorSegments", "id": "48ee51d8-4f64-4a51-a207-84d55995d405", "tagName": "TABC", "tagTitle": "TABC" }, { "groupName": "visitorSegments", "id": "842dc7cf-7d7b-4df6-9dff-f4a14af81027", "tagName": "Test1111", "tagTitle": "Test a - 0" }, { "groupName": "visitorSegments", "id": "92eca3f0-1192-455b-b2cc-69926d5e7c5c", "tagName": "Test a - 1", "tagTitle": "Test a - 1" }, { "groupName": "visitorSegments", "id": "b2a2c298-12ae-4cd4-a94f-b6b846bda583", "tagName": "Int Test 2", "tagTitle": "Int Test 2" } ] }, "type": "pqr" })
SELECT tag.tagTitle
FROM mydb AS d
UNNEST ARRAY_CONCAT(d.tags.`primary`, d.tags.`secondary`) AS tag
WHERE d.type="pqr";

Above one gives me 8 results

6

Yes, my bad while searching I made spelling mistakes in the primay instead of primary. Thanks for the quick response this worked for me.