SELECT dynamic property in array by N1QL query

SQL++
,
1

Is this possible to SELECT property in array,
such as my array look like

["name","type","price"]

I want to query property in array such as

SELECT name,type,price FROM default

where the array is dynamic.

further more,If I use USE KEYS and filter array,can I query dynamic property such as

keys=["id1","id2"]&filter=[["meta().id","key1_for_id1","key2_for_id1"],[["meta().id","key1_for_id2","key2_for_id2","key3_for_id2"]]]

the result should be

[
  {
    "id": "id1",
    "key1_for_id1": "value1",
    "key2_for_id1": "value2"
  },
  {
    "id": "id2",
    "key1_for_id2": "value1",
    "key2_for_id2": "value2"
    "key3_for_id2": "value3"
  }
]
2

@atom_yang, Check this article by @keshav_m https://dzone.com/articles/json-files-whats-in-a-new-york-name-unlocking-data

1 Like
3

Above things to select and name the attributes you need to pass two arrays

  1. To name result attributes

  2. Identifiers to select

    sample document:
    {
    “k0”: 80,
    “k1”: 8,
    “k2”: 0,
    “k3”: “A0080”,
    “k4”: “A0008”,
    “k5”: “A0000”,
    “k6”: true,
    “ka0”: [
    “A080”,
    “A081”,
    “A082”,
    80,
    81
    ],
    “ka1”: [
    “A008”,
    “A081”,
    “A082”,
    8,
    8
    ],
    “km0”: 80
    }

Arrays : [k0,k1,k2] [“k0”,“k1”,“k2”]

select RAW o FROM items LET o = OBJECT p:[k0,k1,k2][ARRAY_POSITION(["k0","k1","k2"],p)] FOR p IN ["k0","k1","k2"] END WHERE k0 = 80;

"results": [
        {
            "k0": 80,
            "k1": 8,
            "k2": 0
        }
    ]
4

Thank you very much.

5

Where is OBJECT function docs?
and as article shows OBJECT will create name:value pairs, in this example,which is name and which is value?

["k0","k1","k2"][ARRAY_POSITION([k0,k1,k2],p)]

is name and p is value?why?

6

OBJECT in this case is not function it is collection similar to ARRAY syntax.
@prasad, Can you check if this documented.
@atom_yang In mean time you can see syntax ARRAY, FIRST, and OBJECT https://github.com/couchbase/query/blob/master/docs/n1ql-select.md

Left side of colon is name which is evaluated and dynamically derived. Right side of colon is value. FOR each element treated as attribute of item and builds json object.

The following is better because names are unique and values are not.

OBJECT p:[k0,k1,k2][ARRAY_POSITION([“k0”,“k1”,“k2”],p)] FOR p IN [“k0”,“k1”,“k2”] END

Both arrays needs to be of same size and we are building name:value pair form both arrays at the same position.

1 Like
7

so p is name ,such as “k0”,“k1”,“k2”, and [k0,k1,k2][ARRAY_POSITION(["k0","k1","k2"],p)] is corresponding value ?
I am still confused why [k0,k1,k2][ARRAY_POSITION(["k0","k1","k2"],p)] is corresponding value from items?

image.png898×310 7.7 KB

8

If p is “k1”
ARRAY_POSITION([“k0”,“k1”,“k2”],p) ===> 1
[k0,k1,k2][ARRAY_POSITION([“k0”,“k1”,“k2”],p)] ===> [k0,k1,k2][1]===> k1
p:[k0,k1,k2][ARRAY_POSITION([“k0”,“k1”,“k2”],p)] ===> “k1”:k1
now k1 is expression (it is not a string, no string quotes) so it evaluates form the document and picks value.

Other easy way is [k0,k1,k2] is array of expressions when evaluated (picks the values from the document) becomes array of values.

1 Like
9

Orz… k1 is expression.so cool!
thank you very much. I understand.

10

it doesn’t seem to be in docs. It should be in https://developer.couchbase.com/documentation/server/4.6/n1ql/n1ql-language-reference/collectionops.html
Will get this fixed. Thanks Atom/Sitaram.

1 Like