I have a property which can have multiple values, but it typically only has one value. It is defined as: Products d:text false true true false both I need to find all the documents that have more than one assigned product. Or: num(sc:productName) > 1 How can I do this in a query?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do I find all the documents with multiple values in a property?

xkahn

I have a property which can have multiple values, but it typically only has one value. It is defined as:

        <property name="sc:productName">
          <title>Products</title>
          <type>d:text</type>
          <mandatory>false</mandatory>
          <multiple>true</multiple>
          <index enabled="true">
            <atomic>true</atomic>
            <stored>false</stored>
            <tokenised>both</tokenised>
          </index>
        </property>

I need to find all the documents that have more than one assigned product. Or:

num(sc:productName) > 1‍

How can I do this in a query?

Find more posts tagged with

Alfresco Content Services

Accepted answers

andy1

The only way you can do that today is with the public API in 5.2.1. This should count the number of titles for each doc.

{
"query": {
    "query": "name:*"
},
"facetFields": {
      "facets": [
         {"field": "DBID", "label": "DBID"}
      ]
    },
"stats": [{
   "field" : "title",
   "label" : "Title"
}],
    "pivots" : [
       {
           "key": "DBID",
           "pivots": [
               {
                   "key": "Title"
               }
               ]
       }
       ]
}

It does not have a way to get rid of counts you do not want.

In earlier versions you could go direct to SOLR and use the JSON facet API where you can expose what would normally be the "having" clause on the aggregate.

It will be a a bit of a pain as you will have to pick the correct field from the real solr schema.

Andy

All comments

afaust

There is no query variant via default Alfresco Java APIs or any of the search languages for this particular use case.

resplin

Axel Faust‌ is usually correct about these things. I will ask around to see if we have a recommendation for this query.

It would help me to better understand the context around this requirement. What causes you to need to do this?

xkahn

I suspect you are right. I'd be happy to use Solr directly if it supported the query.

I guess the best solution is to find all the documents with the property set at all... And then iterate through the results to find the documents I need.

xkahn

Yeah. I'm trying to align the metadata in two different repositories. This property is split in the other repo. (Singleton value property and then multivariate property if more than one value is needed.) I wanted to see how much work this was going to be.

andy1

The only way you can do that today is with the public API in 5.2.1. This should count the number of titles for each doc.

{
"query": {
    "query": "name:*"
},
"facetFields": {
      "facets": [
         {"field": "DBID", "label": "DBID"}
      ]
    },
"stats": [{
   "field" : "title",
   "label" : "Title"
}],
    "pivots" : [
       {
           "key": "DBID",
           "pivots": [
               {
                   "key": "Title"
               }
               ]
       }
       ]
}

It does not have a way to get rid of counts you do not want.

In earlier versions you could go direct to SOLR and use the JSON facet API where you can expose what would normally be the "having" clause on the aggregate.

It will be a a bit of a pain as you will have to pick the correct field from the real solr schema.

Andy

afaust

Well, if you can do that with the public API you should be able to do it with the Java API as well, right?

andy1

Yes, you can do it in the Java API as that is how we implement the public API - but again only on 5.2.1.

In general the public API is cleaner. The Java API has a couple of oddities for things like multi-select facets - but these do not matter for this case.
They were a pain to do in a backwards compatible way with the Java API.

Andy