Insert into from another bucket loads only one record

SQL++
,
1

Hi,

I am trying to insert data from a diff bucket to another bucket, however it loads only the first record, below is the query used,

insert into bucket1 (KEY ‘newkey’,VALUE user_record)
select user_record from diffbucket as user_record where type=‘xyz’;

Actual data in the SELECT query: select user_record from diffbucket as user_record where type=‘xyz’;

[
{
“user_record”: {
“value”: “abc”
}
},
{
“user_record”: {
“value”: “xyz”
}
}
]

Data inserted is just one and the other record is not loaded, any insights on how the query should be tweaked is appreciated, Thanks

{
“value”: “abc”
}

2

The key needs to be unique. After inserting the first document with a key of ‘newkey’, subsequent inserts will fail. You can use the key from the source bucket

insert into bucket1 (KEY the_key,VALUE user_record)
select meta().id the_key, user_record from diffbucket as user_record where type=‘xyz’;

Follow the example at INSERT | Couchbase Docs

1 Like
3

thank you, however is there a way where I can get the output of multiple docs out of a select query and insert them all into one single key; which is what I am sort of trying here.

4

With the source documents as:

INSERT INTO default VALUES("k0",{"a":1,"c":1}),VALUES("k1",{"b":2,"d":2});

You can:

INSERT INTO default
VALUES("newkey1",(SELECT default FROM default WHERE meta().id IN ["k0","k1"]));

which will result in a new document containing a unnamed array of the source documents, i.e.

    SELECT * FROM default WHERE meta().id = 'newkey1';

    "results": [
    {
        "default": [
            {
                "default": {
                    "a": 1,
                    "c": 1
                }
            },
            {
                "default": {
                    "b": 2,
                    "d": 2
                }
            }
        ]
    }
    ],

Variants such as:

INSERT INTO default
VALUES("newkey2",{"docs":(SELECT default.* FROM default WHERE meta().id IN ["k0","k1"])});

return an object containing a named array and remove the inner labelling:

    SELECT default.* FROM default WHERE meta().id = 'newkey2';

    "results": [
    {
        "docs": [
            {
                "a": 1,
                "c": 1
            },
            {
                "b": 2,
                "d": 2
            }
        ]
    }
    ],

If you’re looking to merge the matching documents into one, then:

INSERT INTO default
VALUES("newkey3",OBJECT v.name:v.val 
                 FOR v IN object_pairs((SELECT default.* FROM default WHERE meta().id IN ["k0","k1"])[*]) END);

results in a document that contains all fields from all source documents, with each field’s value being an array of values, one from each document with null as the placeholder if a field didn’t appear in a document:

    SELECT default.* FROM default WHERE meta().id = 'newkey3';

    "results": [
    {
        "a": [
            null,
            1
        ],
        "b": [
            2,
            null
        ],
        "c": [
            null,
            1
        ],
        "d": [
            2,
            null
        ]
    }
    ],

The nulls can be stripped with:

INSERT INTO default 
VALUES("newkey4",OBJECT v.name:ARRAY a FOR a IN v.val WHEN a IS NOT NULL END 
                 FOR v IN object_pairs((SELECT default.* FROM default WHERE meta().id IN ["k0","k1"])[*]) END);

yielding:

    SELECT default.* FROM default WHERE meta().id = 'newkey4';

    "results": [
    {
        "a": [
            1
        ],
        "b": [
            2
        ],
        "c": [
            1
        ],
        "d": [
            2
        ]
    }
    ],

If you know you have only unique fields or care only for the first value for each:

INSERT INTO default VALUES("newkey5",OBJECT v.name:ARRAY a FOR a IN v.val WHEN a IS NOT NULL END[0] FOR v IN object_pairs((SELECT default.* FROM default WHERE meta().id IN ["k0","k1"])[*]) END);

or

INSERT INTO default VALUES("newkey5",OBJECT v.name:FIRST a FOR a IN v.val WHEN a IS NOT NULL END FOR v IN object_pairs((SELECT default.* FROM default WHERE meta().id IN ["k0","k1"])[*]) END);

yields:

    SELECT default.* FROM default WHERE meta().id = 'newkey5';

    "results": [
    {
        "a": 1,
        "b": 2,
        "c": 1,
        "d": 2
    }
    ],

(You could make this more complex with a CASE statement to unpack the array only when there is a single value etc.)

HTH.

1 Like
5

Thank you that was helpful

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.