Access last element of nested array

SQL++
,
1

I have a structure resembling this:

"balances":[
    { 
        
       "date": "21-09-2021",
       "events":[
        {
          "custId":"TheChosenOne",
          "balance": 394
        },
        {
          "custId":"Ginger",
          "balance": 191
        }
        ]
    },
    {
        "date": "24-09-2021",
        "events":[
        {
        "custId": null,
        "balance": 100
        }
        ]
    }
]

I need to find all docs in which the last entry of balances has an event with custID as null .

I have an index:

CREATE INDEX `adv_ALL_balances` ON `default`((all (`balances`)))

But the query I’m trying isn’t returning any docs.

SELECT * FROM default 
UNNEST balances s
WHERE s.events[-1].custId IN [null]

Couchbase newbie here, seeking help with this :slight_smile:

2

Comparing NULL and MISSING values

The normal comparison operators cannot be used to check for NULL or MISSING values because they do not contain type information. Instead the following operators are designed specifically to work for these values.

IS NULL - returns rows where the value of a property is explicitly set to NULL (not missing).
IS NOT NULL - returns rows which contain a value (not NULL or missing).
IS MISSING - returns rows where the value of a property is missing (not explicitly set to null).
IS NOT MISSING - returns rows which contain a value or null.
IS VALUED - synonym for IS NOT NULL
IS NOT VALUED - synonym for IS NULL
NOTE: IS NULL/IS NOT NULL and IS MISSING/IS NOT MISSING are NOT inverse operators. See table below:

Screen Shot 2021-09-25 at 12.16.19 PM
Screen Shot 2021-09-25 at 12.16.19 PM1152Ă—626 47.2 KB 

CREATE INDEX  ix1 ON `points`( DISTINCT ARRAY  v.events[-1].custId FOR v IN `balances` END ); 

SELECT d.* 
FROM default AS d
WHERE ANY v IN d.`balances` SATISFIES v.events[-1].custId IS NULL END;
1 Like
3

Thanks a ton for the reference!
But the index you suggested is throwing an error :frowning_face:

    {
      "code": 3000,
      "msg": "syntax error - at FOR"  
    }
4

Try:

CREATE INDEX  ix1 ON `points`( DISTINCT ARRAY  v.events[-1].custId FOR v IN `balances` END );

HTH.

2 Likes
5

That helped indeed, thank you :slight_smile:

6

Sorry, this worked well in staging, but on production.
It’ s saying this :frowning:
{"No data to display":"Hit execute to run query."}

7

Have you verified your index creation is complete & the index is available? (In the production cluster’s UI Index section, you should see a “ready” status for the index.)

If your asynchronous index creation has completed, then it sounds like there isn’t data matching your statement in production.

Have you tried to run the query through cbq or the REST interface (as well as the UI) ? (They should indicate the result count explicitly.)

HTH.

8

Index is indeed ready. Didn’t try REST interface or cbq.

Could you please tell how to connect to a cluster that I’m currently accessing via Bastion AWS. The cluster is run on a set of ec2 instances.
I’m tunnelling to it via Bastion.

9

Perhaps the alternative query interface is an unnecessary distraction from your issue; I’d only hoped it a quick test to verify.

Can you confirm with an unfiltered select (or other means - perhaps application?) that you have documents in production meeting the query criteria? (i.e. with null “custId” “balances” array elements.)
– All I’m trying to say is that if the query works in staging (where you know you have data that meets the filtering criteria) it should work in production if production has similar data that matches the criteria, and the most likely explanation is simply that the data is different.

Does the explain plan for the statement on production match that on staging? – both showing this index in use?