updating Couchbase doc with value derived from key

Question

I'm trying to update a Couchbase doc from one structure to another here is the current structure

{
    "config": {
        "160x600": {
            "siteId": "123455677"
        },
        "300x250": {
            "siteId": "123455677"
        },
        "300x600": {
            "siteId": "123455677"
        }
    }
}

Desired structure is

{
    "config": {
        "160x600": {
            "siteId": "123455677",
            "size":[160,600]
        },
        "300x250": {
            "siteId": "123455677",
            "size" : [300,250]
        },
        "300x600": {
            "siteId": "123455677",
            "size": [300,600]
        }
    }
}

Basically I wants to iterate over the keys inside config, split each key on 'x' and assign the resulting array as a value to "size" inside config[key].

Here is an N1QL query I tried (which obviously didn't work, hence this question here);

update AppBucket a set a.config[`size`].size = split(size, `x`) for size in OBJECT_NAMES(a.config) end
where meta(a).id like 'cnfg::40792';

Thanks in advance.

Does it need to be N1QL? If you know the document keys in advance, it would probably be easier and possibly more performant to use the key-value API for this kind of complex JSON wrangling. — Graham Pople
There are hundreds of these docs, so updating them manually is going to be quite a task. I was hoping if it could be solved by a N1QL query, otherwise the last resort is to write a service to fetch all these docs, manipulate them and the update them in couchBase. Thanks. — panghal0

vsr vsr · Accepted Answer · 2020-02-24T13:36:44

If you need to access the dynamic field you must use array brackets immediately after dot (map access) i.e config.[v] and v must be string or evaluate to string. It evaluates expression inside array brackets, it must be string, then converts into identifier and substitutes it and evaluate rest of the path. ex: config.["xyz"] ==> config.xyz , config.[f1] (f1 is "field1") ===> config.field1

Also split key must be string not identifier i.e. "x"

INSERT INTO default VALUES ("cnfg::40792", { "config": { "160x600": { "siteId": "123455677", "size":[160,600] }, "300x250": { "siteId": "123455677", "size" : [300,250] }, "300x600": { "siteId": "123455677", "size": [300,600] } } });

UPDATE default AS a USE KEYS "cnfg::40792"
SET a.config.[v].size = SPLIT(v, "x") FOR v IN OBJECT_NAMES(a.config) END;

The updated ARRAY will have strings i.e ["160","600"]. If you need numbers use the following command. If string can't convert to number it will converts to NULL.

UPDATE default AS a USE KEYS "cnfg::40792"
SET a.config.[v].size = ARRAY TO_NUMBER(v1) FOR v1 IN SPLIT(v, "x") END
           FOR v IN OBJECT_NAMES(a.config) END;

updating Couchbase doc with value derived from key

1 Answers