How to search on properties aggregated by op.arrayAggregate

Question

For this input data:

{
    "document1": [
        {
            "data": {
                "rownum": "1",
                "CustomerName": "Name1",
                "AddrTypeCd": "MailingAddress",
                "Addr1": "911 FORBES AVE",
                "Addr2": "SUITE XXX",
                "CityName": "asdfasfd",
                "StateProvCd": "PA",
                "PostalCode": "15219"
            }
        },
        {
            "data": {
                "rownum": "2",
                "CustomerName": "Name1",
                "AddrTypeCd": "OfficeAddress",
                "Addr1": "911 Watson AVE",
                "Addr2": "SUITE XXX",
                "CityName": "asdfasfd",
                "StateProvCd": "CT",
                "PostalCode": "15119"

            }
        },
{
            "data": {
                "rownum": "3",
                "CustomerName": "Name2",
                "AddrTypeCd": "Nice address",
                "Addr1": "Nice address1",
                "Addr2": "Nice address2",
                "CityName": "City2",
                "StateProvCd": "TEX",
                "PostalCode": "99999"

            }
        }
    ]
}

And aggregate it in this way:

op.fromView('main', 'CustomerAddress')
    .select(['Addr1',
             'Addr2',
             'CityName',
             'StateProvCd',
             'CustomerName',   
              op.as('addrAggr', op.jsonObject([
                op.prop('Addr1', op.col('Addr1')),
                op.prop('Addr2',  op.col('Addr2')),
                op.prop('CityName', op.col('CityName')),
                op.prop('StateProvCd', op.col('StateProvCd'))
              ]))
          ])
    .orderBy(op.asc('Addr1'))   //Line14
    .groupBy('CustomerName', op.arrayAggregate("Customer addresses", "addrAggr"))
    .result();

And this is the result of above method:

[
    {
        "CustomerName": "Name1", 
        "Customer addresses": [
            {
                "Addr1": "911 FORBES AVE", 
                "Addr2": "SUITE XXX", 
                "CityName": "asdfasfd", 
                "StateProvCd": "PA"
            }, 
            {
                "Addr1": "911 Watson AVE", 
                "Addr2": "SUITE XXX", 
                "CityName": "asdfasfd", 
                "StateProvCd": "CT"
            }
        ]
    },
    {
        "CustomerName": "Name2", 
        "Customer addresses": [
            {
                "Addr1": "Nice address", 
                "Addr2": "Nice address2",
                "CityName": "City2",
                "StateProvCd": "TEX"
            }
        ]
    }
]

Now I need to do search on results that return me all objects after grouping where Addr1 = "911 Watson AVE". The behavior I want to receive is similar to HAVING in SQL. So the result at the end should look like this:

{
        "CustomerName": "Name1", 
        "Customer addresses": [
            {
                "Addr1": "911 FORBES AVE", 
                "Addr2": "SUITE XXX", 
                "CityName": "asdfasfd", 
                "StateProvCd": "PA"
            }, 
            {
                "Addr1": "911 Watson AVE", 
                "Addr2": "SUITE XXX", 
                "CityName": "asdfasfd", 
                "StateProvCd": "CT"
            }
        ]
    }

Answer 1

I think your best bet for this is to first create a view with the CustomerNames that you want. Then create a second view based on your view above. Then join the views. To test this in qconsole, I include your example data as a literal (thanks Mads)

'use strict';
const op = require('/MarkLogic/optic');

let data =
  [
    {
      "rownum": "1",
      "CustomerName": "Name1",
      "AddrTypeCd": "MailingAddress",
      "Addr1": "911 FORBES AVE",
      "Addr2": "SUITE XXX",
      "CityName": "asdfasfd",
      "StateProvCd": "PA",
      "PostalCode": "15219"
    },
    {
      "rownum": "2",
      "CustomerName": "Name1",
      "AddrTypeCd": "OfficeAddress",
      "Addr1": "911 Watson AVE",
      "Addr2": "SUITE XXX",
      "CityName": "asdfasfd",
      "StateProvCd": "CT",
      "PostalCode": "15119"
    },
    {
      "rownum": "3",
      "CustomerName": "Name2",
      "AddrTypeCd": "Nice address",
      "Addr1": "Nice address1",
      "Addr2": "Nice address2",
      "CityName": "City2",
      "StateProvCd": "TEX",
      "PostalCode": "99999"
    }
  ];

Then I build the left side of the join from your data, filtering on Addr1

let leftSide =
  op.fromLiterals(data)
    .select([
      'Addr1',
      'CustomerName',
    ])
    .where(op.eq(op.col('Addr1'), "911 Watson AVE"))

Next, I use your query from above to build the right side (I build it within

let rightSide =
  op.fromLiterals(data)
    .select([
      'Addr1',
      'Addr2',
      'CityName',
      'StateProvCd',
      'CustomerName',   
      op.as(
        'addrAggr',
        op.jsonObject([
          op.prop('Addr1', op.col('Addr1')),
          op.prop('Addr2',  op.col('Addr2')),
          op.prop('CityName', op.col('CityName')),
          op.prop('StateProvCd', op.col('StateProvCd'))
        ])
      )
    ])
    .orderBy(op.asc('Addr1'))
    .groupBy('CustomerName', op.arrayAggregate("Customer addresses", "addrAggr"))

Finally, I join the two views and get the result

leftSide.joinLeftOuter(
  rightSide,
  op.on(op.col('CustomerName'), op.col('CustomerName'))
)
  .select([
    'CustomerName',
    "Customer addresses"
  ])
  .result();