Add Facets to Query Python SDK

[nelsonxx1]

I need to add Facet for Group By max 1 hit for record.
Couchbase Community 7.0
Python SDK 3.2.7

indexName = 'index_table'

conjunction = []

date_ss = search.DateRangeQuery(start='2020-01-01', end='2020-01-31', start_inclusive=True, end_inclusive=True, field='actual_arrival_date')

conjunction.append(date_ss)

conjunction.append(search.MatchQuery(term='ningbo bingo', field='containers.loads.description_text'))

options = {'limit':20, 'skip':0}

conjun = search.ConjunctionQuery(*conjunction)

try:

    # result = cluster.search_query(index=indexName,query=conjun, options=options)

    result = cluster.search_query(index=indexName,query=conjun, **options)

    print(result)

    for row in result.rows():

        print(row)

        # collection_bills.get(row.id).content_as[dict]

        # print("Found row: {}".format(row.id))

        # print(collection_bills.get(row.id).content_as[dict]['foreign_port_of_lading_name'])

       

        #print(collection_bills.get(row.id).content_as[dict]['port_of_unlading_name'])

        # print(collection_bills.get(row.id).content_as[dict]['master_bol_number'])

        # print(collection_bills.get(row.id).content_as[dict]['master_bol_number'])

    print("Reported total rows: {}".format(

        result.metadata().metrics.total_rows))

except CouchbaseException as ex:

    import traceback

    traceback.print_exc()

[nelsonxx1]

Do something like this.

SELECT portName, COUNT(*) FROM Ports GROUP BY portName.

apply this on filter results, via Facets fields

[nelsonxx1]

Hi @abhinav @sreeks please help me

[abhinav]

@nelsonxx1 I’m not sure I entirely understand your question here.

Search requests that involve facets are not supported from N1QL. FTS only forwards the “hits” part from the search response to the N1QL engine. For facetted search requests, you should go directly to the search service (via REST or SDK) - Search Request | Couchbase Docs

If you’re having trouble with the SDK, I’d refer you to our SDK experts on this - #python-sdk .

[nelsonxx1]

@abhinav
Ok I want the results of a search with FTS to have Facets Fields that count the number of records that are the same. The whole field, not participating.

Example

Record 1
Record 2
Record 2
Record 3

Result:

Record 1: count 1
Record 2: count 2
Record 3: count 1

It doesn’t matter if I have to do it by API

[abhinav]

Ok, let’s say fieldX is the field name that holds “Record …” for every document.
Based on the data you have, I’d recommend indexing the “fieldX” using the keyword analyzer within your index - include doc values for it.

Here’s how your search request will look …

{
  "query": {
    "match_all": {}
  },
  "facets": {
    "fieldX": {
      "size": 3,
      "field": "fieldX"
    }
  }
}

I’ve selected size: 3 - which will emit the top 3 facets (aggregations) over the field: fieldX. The facets output would look like this …

"facets": {
  "fieldX": {
    "field": "fieldX",
    "total": 4,
    "missing": 0,
    "other": 0,
    "terms": [
      {
        "term": "Record 2",
        "count": 2
      },
      {
        "term": "Record 1",
        "count": 1
      },
      {
        "term": "Record 3",
        "count": 1
      }
    ]
  }
}

Here’s documentation on this: Search Request | Couchbase Docs

[nelsonxx1]

@abhinav
It is possible to use more than an Analyzer in the same field and in the same FTS index ?? For Faces fields use the keyword, but at the same time the data can be filtered with separate words by blank spaces, point, coma, etc …

[abhinav]

Yes that’s possible. You will need to use the “searchable as” option to set a field alias. The field aliases will need to be unique within a type mapping.

• fieldX with analyzer1 and make it searchable as “fieldX-1”
• fieldX with analyzer2 and make it searchable as “fieldX-2”

[nelsonxx1]

@abhinav
please, send me one, example in dashhborad. I can not see that option in the FTS

[sreeks]

Vs

As long as you search against those chosen AnySuitableSearchName-N, it would use the specified analyzer for that query as long as it is a non-analytic query in FTS.

[nelsonxx1]

@sreeks @abhinav

I was testing the facet option and it works fine, but is it possible to add the total amount of facet responses? for no to add a size: 9999999999, I see that you do the tour of all the records, could this answer be included in the out of the facet field?

Example: total_records_facet: 10255

[abhinav]

The total attribute of the facet result indicates the number of records that have been accounted for in the generated facets (based on the size).

We mean for users to set size to obtain the top-N facets. The only way to emit all the facets there are - is by choosing a large enough size and hoping that it’s greater than the number of facets.

[nelsonxx1]

with sql it would be something like this, but it takes too long time.

SELECT COUNT(1) FROM
(SELECT fpol, COUNT(1) AS count
FROM bills AS b
LET fmeta = SEARCH_META(b)
WHERE SEARCH(b, 
{
  "fields": [
    "foreign_port_of_lading_name"
  ],
  "query": {
    "conjuncts": [
      {
        "inclusive_start": true,
        "inclusive_end": true,
        "field": "actual_arrival_date",
        "start": "2020-01-01",
        "end": "2020-01-31"
      },
      {
        "field": "foreign_port_of_lading_name",
        "match_phrase": "china"
      }
    ]
  }
},
{"index": "index_table3"}
)
GROUP BY fmeta.fields.foreign_port_of_lading_name AS fpol) AS v

@sreeks @abhinav
Is there an optimal way to do this?