INSERT using SELECT?

SQL++
1

Hi,

I’m fairly new to N1QL, but I’m just trying to do a simple INSERT using SELECT to create a counter.

Something like

INSERT INTO dp (KEY mykey, VALUE count)
SELECT ‘AAR-COUNTER’ as mykey, COUNT(*) as count from dp where type = ‘AAR’;

What I’m looking to do is ultimately create a simple document like so:

{
count : 6
}
which has an meta().id of ‘AAR-COUNTER’

What am I doing wrong? Doing the query as I have typed it results in:

[
{
“code”: 3100,
“msg”: “Aggregates/Window functions are allowed in SELECT only”,
“query”: “UPSERT INTO dp (KEY mykey, VALUE count)\r\nSELECT ‘AAR-COUNTER’ as mykey, COUNT(*) as count from dp where type = ‘AAR’;”
}
]

2

So the best approach I’ve found so far is to do something like this:

INSERT INTO dp ( KEY, VALUE )
VALUES
(
“AAR-COUNTER”,
{ “count”: (select raw count(meta().id) from dp where type = ‘AAR’)}
)

However, this is still not optimal, since what I get is a document that looks like this:

{
“count”: [
6
]
}

When I want just

{
“count”: 6
}

This is due to the select statement returning everything wrapped in an array object. Is it possible to life the value out of the array from the SELECT statement?

3

Fixed in the next release (6.5.1) MB-37470

use like this

    INSERT INTO dp ( KEY, VALUE )
    VALUES (“AAR-COUNTER”, { “count”: (select raw count(meta().id) from dp where type = ‘AAR’) [0]} )
1 Like
4

Okay, I looked at the JIRA issue this is tracked in (MB-37470)

and just wanted to give some feedback.

This statement definitely does not work in 6.5 - the latest release:

INSERT INTO dp( KEY “AAR-COUNTER4”, VALUE {doc} )
SELECT COUNT(meta().id) as doc FROM dp where type = “AAR”;

We get the same error:

[
{
“code”: 3100,
“msg”: “Aggregates/Window functions are allowed in SELECT only”,
“query”: “INSERT INTO dp( KEY “AAR-COUNTER4”, VALUE {doc} )\r\nSELECT COUNT(meta().id) as doc FROM dp where type = “AAR”;”
}
]

Even with the fix that Sitaram described - wrapping doc in {doc}. I tried variations of aggregate functions (SUM(1)…etc) It does not let you have a INSERT INTO using SELECT projecting a JSON document in if you use the ( Key foo, VALUE bar ) syntax.

Using the alternative syntax as you described above works.

5

MB-37470 Affected versions has 6.5.0 (problem is exist in 6.5.0). Fixed version has 6.5.1 (Fixed in 6.5.1)
6.5.1 is not released, will be released some time near future.

The problem (bug) is processing INSERT/UPSERT INTO … SELECT …
Alternative syntax that working is INSERT statement. value has expressions as subquery. As SELECT produces single document , alternative syntax possible. As you could come up, it is good alternative. Otherwise it may not possible.