FTS query to perform equivalent of N1QL with ANY ... SATISFIES ... AND ... END

Full Text Search
1

I’d like to get input on having a FTS equivalent to following N1QL… Can someone help with this?

N1QL:

select * from `data-container` as dc where any e in dc.ent satisfies e.con in ["us", "in"] 
and e.det in ["all", "streaming"]
and STR_TO_MILLIS(e.f_ed_dt) > CLOCK_MILLIS() 
and STR_TO_MILLIS(e.f_st_dt) <= CLOCK_MILLIS() 
and ARRAY_LENGTH(ARRAY_INTERSECT(e.sp, ["monthly-plan", "all"])) > 0 
and ARRAY_LENGTH(ARRAY_INTERSECT(e.dt, ["iosmobile", "all"])) > 0 end

Data:

{
  "ent": [
    {
      "con": "in",
      "det": "streaming",
      "dt": [
        "iosmobile"
      ],
      "f_ed_dt": "2022-01-01T00:00:00Z",
      "f_st_dt": "2021-01-01T00:00:00Z",
      "sp": [
        "monthly-plan"
      ]
    },
    {
      "con": "in",
      "det": "all",
      "dt": [
        "androidmobile"
      ],
      "f_ed_dt": "2022-01-01T00:00:00Z",
      "f_st_dt": "2021-01-01T00:00:00Z",
      "sp": [
        "monthly-plan"
      ]
    }
  ]
}
2
  • You’ll first want to make sure that you have at least 1 node in the cluster hosting the search service.
  • Next you need to set up an FTS index that can support the query. Add this json to a file fts.json …
{
  "type": "fulltext-index",
  "name": "fts-data-container",
  "sourceType": "couchbase",
  "sourceName": "data-container",
  "planParams": {
    "indexPartitions": 1
  },
  "params": {
    "doc_config": {
      "mode": "type_field",
      "type_field": "type"
    },
    "mapping": {
      "analysis": {},
      "default_analyzer": "standard",
      "default_datetime_parser": "dateTimeOptional",
      "default_field": "_all",
      "default_mapping": {
        "dynamic": false,
        "enabled": true,
        "properties": {
          "con": {
            "dynamic": false,
            "enabled": true,
            "fields": [
              {
                "analyzer": "keyword",
                "index": true,
                "name": "con",
                "type": "text"
              }
            ]
          },
          "det": {
            "dynamic": false,
            "enabled": true,
            "fields": [
              {
                "analyzer": "keyword",
                "index": true,
                "name": "det",
                "type": "text"
              }
            ]
          },
          "dt": {
            "dynamic": false,
            "enabled": true,
            "fields": [
              {
                "analyzer": "keyword",
                "index": true,
                "name": "dt",
                "type": "text"
              }
            ]
          },
          "f_ed_dt": {
            "dynamic": false,
            "enabled": true,
            "fields": [
              {
                "index": true,
                "name": "f_ed_dt",
                "type": "datetime"
              }
            ]
          },
          "f_st_dt": {
            "dynamic": false,
            "enabled": true,
            "fields": [
              {
                "index": true,
                "name": "f_st_dt",
                "type": "datetime"
              }
            ]
          },
          "sp": {
            "dynamic": false,
            "enabled": true,
            "fields": [
              {
                "analyzer": "keyword",
                "index": true,
                "name": "sp",
                "type": "text"
              }
            ]
          }
        }
      },
      "default_type": "_default",
      "type_field": "_type"
    },
    "store": {
      "indexType": "scorch"
    }
  },
  "sourceParams": {}
}
  • Now run this command to introduce the FTS index into your system …
curl -XPUT -H "Content-type:application/json" -u <username>:<password>
http://<ip>:8094/api/index/fts-data-container -d @fts.json
  • The FTS index above assumes that this is your document structure …
{
  "con": "in",
  "det": "streaming",
  "dt": [
    "iosmobile"
  ],
  "f_ed_dt": "2022-01-01T00:00:00Z",
  "f_st_dt": "2021-01-01T00:00:00Z",
  "sp": [
    "monthly-plan"
  ]
}
  • Now assuming that you have already defined the N1QL functions you’re using, here’s a N1QL query that will use the FTS index …
SELECT meta.id FROM data-container AS dc
LET meta=search_meta()
WHERE SEARCH(dc,
{
  "query": {
    "conjuncts": [
      {
        "disjuncts": [
          {
            "match": "us",
            "field": "con"
          },
          {
            "match": "in",
            "field": "con"
          }
        ]
      },
      {
        "disjuncts": [
          {
            "match": "all",
            "field": "det"
          },
          {
            "match": "streaming",
            "field": "det"
          }
        ]
      },
      {
        "disjuncts": [
         {
            "match": "monthly-plan",
            "field": "sp"
          },
         {
            "match": "all",
            "field": "sp"
          }        
        ]
      },
      {
        "disjuncts": [
         {
            "match": "iosmobile",
            "field": "dt"
          },
         {
            "match": "all",
            "field": "dt"
          }       
        ]
      },
      {
         "field": "f_ed_dt",
         "end": "<SPECIFY A TIME HERE IN ISO-8601 format>",
         "inclusive_end": true
      },
      {
         "field": "f_ed_dt",
         "start": "<SPECIFY A TIME HERE IN ISO-8601 format>"
      }
    ]
  }
}, {"index": "fts-data-container"});

Optionally, you can run this query directly against the FTS server as …

curl -XPOST -H "Content-type:application/json" -u <username>:<password>
http://<ip>:8094/api/index/fts-data-container/query -d
`<paste the 2nd argument of the SEARCH function above here>`
3

Thanks for the super quick response @abhinav . The document structure is nested as mentioned initially and it can have other attributes at root level for other filter conditions as well. The request is to filter a document which satisfies root level attribute conditions and any one object inside the ent matches all ent specific conditions. Planning to use direct FTS query instead of N1QL with FTS query.

select * from `data-container` as dc where any e in dc.ent satisfies e.con in ["us", "in"] 
and e.det in ["all", "streaming"]
  and STR_TO_MILLIS(e.f_ed_dt) > CLOCK_MILLIS() 
  and STR_TO_MILLIS(e.f_st_dt) <= CLOCK_MILLIS() 
  and ARRAY_LENGTH(ARRAY_INTERSECT(e.sp, ["monthly-plan", "all"])) > 0 
  and ARRAY_LENGTH(ARRAY_INTERSECT(e.dt, ["iosmobile", "all"])) > 0 end
and ARRAY_LENGTH(ARRAY_INTERSECT(qty, ["HD"])) > 0

{
  "qty": ["HD", "SD"],
  "ent": [
    {
      "con": "in",
      "det": "streaming",
      "dt": [
        "iosmobile"
      ],
      "f_ed_dt": "2022-01-01T00:00:00Z",
      "f_st_dt": "2021-01-01T00:00:00Z",
      "sp": [
        "monthly-plan"
      ]
    },
    {
      "con": "in",
      "det": "all",
      "dt": [
        "androidmobile"
      ],
      "f_ed_dt": "2022-01-01T00:00:00Z",
      "f_st_dt": "2021-01-01T00:00:00Z",
      "sp": [
        "monthly-plan"
      ]
    }
  ]
}
4

Cool. In that case you’ll need to set up “ent” as a child mapping in your index and within it child fields …

default            (type mapping)
 | --> ent         (child mapping)
       | --> con   (child field)       .. searchable as: ent.con
       | --> det   (child field)       .. searchable as: ent.det
       | --> dt    (child field)       .. searchable as: ent.dt
       | --> sp    (child field)       .. searchable as: ent.sp