Hash Maps / Dictionaries in a N1QL Query

SQL++
,
1

Hello!

We have a complex query which performance is really laggy and we want to know if there are any dictionary support in Couchbase.

For now, we pass in the list of JSON values called “upsertMetadata” which contains a bunch of documents that we want to update in a specific way.

Here is an example of our code:

assetMetadata.fields[i].`values` = ARRAY_CONCAT(assetMetadata.fields[i].`values`,
(($metadata)[ARRAY_POSITION((select raw upsertMetadata.assetId from $metadata as upsertMetadata), assetMetadata.assetId)]
.fields[ARRAY_POSITION((select raw upsertField.name from $metadata as upsertMetadata
UNNEST upsertMetadata.fields as upsertField where upsertMetadata.assetId = assetMetadata.assetId), field.name)]).`values`)
    for i: field in assetMetadata.fields
    when ARRAY_CONTAINS((select raw upsertField.name from $metadata as upsertMetadata UNNEST upsertMetadata.fields as upsertField
    where upsertMetadata.assetId = assetMetadata.assetId), field.name) END

This code basically updates the values in an existing array field of an array field of our record.
For now, for 5K new values, it takes 125ms to update one value which is quite a lot.

As you can see, it runs through our passed “$metadata” a couple of times. We could’ve sped it up by using a dictionary in our query, but I was not able to find any examples of dictionary query parameters in documentation.

I want to achieve behaviour like this:

select tt["James"] from {
  "James": 9001,
  "Jo": 3474,
  "Jess": 11926
} tt

9001

I’ve tried to use a “USE KEYS” expression but it doesn’t work with the query parameters. It says: “FROM Expression cannot have USE KEYS or USE INDEX or join hint (USE HASH or USE NL). - at end of input”

2

You have provided partial expression and it is hard to understand. It looks like too complex. If you can provide sample document , sample query parameter and what you want achieve will see any better way to do this.

Couchbase already provides dictionary and map access in the N1QL. Required dot before array bracket. Checkout link below for explanation.

select tt.["James"] from {
  "James": 9001,
  "Jo": 3474,
  "Jess": 11926
} tt
3

Thank you for your answer!

The full query looks like this:

update assets as assetMetadata
use keys $keys
set
    assetMetadata.fields[i].`values` = ARRAY_CONCAT(assetMetadata.fields[i].`values`,
    (($metadata)[ARRAY_POSITION((select raw upsertMetadata.assetId from $metadata as upsertMetadata), assetMetadata.assetId)]
    .fields[ARRAY_POSITION((select raw upsertField.name from $metadata as upsertMetadata
    UNNEST upsertMetadata.fields as upsertField where upsertMetadata.assetId = assetMetadata.assetId), field.name)]).`values`)
        for i: field in assetMetadata.fields
        when ARRAY_CONTAINS((select raw upsertField.name from $metadata as upsertMetadata UNNEST upsertMetadata.fields as upsertField
        where upsertMetadata.assetId = assetMetadata.assetId), field.name) END,
    assetMetadata.fields[ARRAY_LENGTH(assetMetadata.fields)] = 
    {
                        ""name"": upsertField.name,
                        ""values"": ARRAY {
                        ""value"": upsertValue.`value`,
                        ""version"": assetMetadata.version,
                        ""source"": upsertValue.source
                }
                FOR upsertValue
                IN upsertField.`values` END
    }
        for upsertField in 
        (select raw upsertField from $metadata as upsertMetadata 
        UNNEST upsertMetadata.fields as upsertField
        where upsertMetadata.assetId = assetMetadata.assetId)
        when not ARRAY_CONTAINS(
        (select raw field.name from assets as am 
        use keys $keys
        unnest am.fields as field
        where am.assetId = assetMetadata.assetId)
        , upsertField.name) END,
    field.`values`[ARRAY_POSITION(field.`values`, field)].version = 
    CASE WHEN assetMetadata.version is not null then assetMetadata.version else 1 end
        for field in assetMetadata.fields
        when ARRAY_CONTAINS((select raw upsertField.name from $metadata as upsertMetadata UNNEST upsertMetadata.fields as upsertField
        where upsertMetadata.assetId = assetMetadata.assetId), field.name) 
        END

We want to add the “value” to an existing “field” or add a new field if it doesn’t exist in our document. Also, we want to update the version for every value that we’ve added.
We take all the new fields/values data from a set of “update documents” that we receive from our serverside code.

Here’s the sample “update document”:

    {
        "assetId": "someGuid",
        "schemaId": "someGuid",
        "version": 0,
        "fields": [
            {
                "name": "test1",
                "values": [
                    {
                        "value": "test",
                        "version": 0,
                        "source": 0
                      }
                ]
            }
        ]
    }

The query contains a lot of mutations as I wasn’t quite sure about how to perform a complex update in N1QL in a way different from this one.

4 
UPDATE assets AS am
USE KEYS $keys
SET amf.`values` = ARRAY_CONCAT(amf.`values`, ARRAY OBJECT_ADD(v,"version", IFMISSINGORNULL(am.version,1)) FOR v IN umf.`values` END)
    FOR amf IN am.fields
        FOR umf IN um.fields
            FOR um IN (ARRAY um1 FOR um1 IN $metadata WHEN um1.assetId = am.assetId END)
    WHEN amf.name = umf.name END,
    am.fields = ARRAY_APPEND(am.fields, {v.name,"values": ARRAY {v.`value`,v.`source`,"version":IFMISSINGORNULL(am.version,1)} FOR v in umf.`values` END})
        FOR umf IN um.fields
            FOR um IN (ARRAY um1 FOR um1 IN $metadata WHEN um1.assetId = am.assetId END)
    WHEN umf.name NOT IN am.fields[*].name END,
WHERE ANY um IN $metadata SATISFIES um.assetId = am.assetId END;

If you change $metadata as map like below

{
"assetid1":{ "fname1": [], "fname2": [] },
"assetid2":{ "fname1": [] }
}


UPDATE assets AS am
USE KEYS $keys
SET amf.`values` = ARRAY_CONCAT(amf.`values`, ARRAY OBJECT_ADD(v,"version", IFMISSINGORNULL(am.version,1)) FOR v IN umf.val END)
    FOR amf IN am.fields
        FOR umf IN OBJECT_PAIRS($metadata.[am.assetId])
    WHEN amf.name = umf.name END,
    am.fields = ARRAY_APPEND(am.fields,  {v.name,"values": ARRAY {v.`value`,v.`source`,"version":IFMISSINGORNULL(am.version,1)} FOR v in umf.val END})
        FOR umf IN OBJECT_PAIRS($metadata.[am.assetId])
    WHEN umf.name NOT IN am.fields[*].name END,
WHERE $metadata.[am.assetId] IS NOT NULL;