Can we parametric json key IN prepared?

N1QL
,
1

for example, My sample N1QL

SELECT name FROM default d WHERE d.type == "USR"

I want to parametric d.type to $type, such as

curl -v http://127.0.0.1:8093/query/service --data-urlencode 'statement=PREPARE T1 FROM SELECT  name  FROM default t WHERE t.`$type` == "USR";'

and it created success.

and I want to query data by

curl -v http://127.0.0.1:8093/query/service -d 'prepared="T1"&$type="type"'

it return [].

2

Not attribute. You can do values.
Like WHERE type = $type_value

3

is there a plan for support parametric JSON key?

4

@geraldss, Any comments.

5

I think it is very useful for complicated JSON data structure,such as following example data:

{
  "user_list":{
    "user:1":{"login_time":1482828941668,"device":"pc"},
    "user:2":{"login_time":1482828942668,"device":"mobile"}
  }
}

it is very normal usage for JSON.it will be good if N1QL can support parametric JSON key, ```user_list.`$user_id```` here for example.

6

Yes, this is supported.

user_list.[$user_id]

cc @prasad for documentation.

7

Also you can try this

$user_id IN OBJECT_NAMES(`user_list`)
OR 
ANY v IN OBJECT_NAMES(`user_list`) SATISFIES v = $user_id END
8

the sample data of $user_id is “USR:1”, which should be escape with ` ,how should I write parametric key?

and if I have a condition such as

user_list.`$user_id`.device == "pc"

how should I write parametric key?

9

Did you try out the syntax?

10

when I add ` by

user_list.[`$user_id`].device == "pc"

I got the following error

    "errors": [
        {
            "code": 3000,
            "msg": "Ambiguous reference to field $user_id."
        }
    ]

and If I don’t add ` by

user_list.[$user_id].device == "pc"

I will not query any data.

11

for this use case,I can query data by

SELECT user_list FROM default 
WHERE ANY v IN OBJECT_NAMES(`user_list`) SATISFIES v = "user:1" END
  AND ANY u IN OBJECT_VALUES(`user_list`) SATISFIES u.device = "pc" END

with @vsr1 's suggestion.
but how should I only query login_time that match my condition, here should be user_list.`usr:1`.login_time

12 
SELECT user_list FROM default 
WHERE  ANY v IN OBJECT_PAIRS(user_list) SATISFIES v.name = "user:1" AND v.val.login_time =1482828941668END;
13

It should work without the back ticks…

14

I want to only SELECT login_time for match ANY v IN OBJECT_PAIRS(user_list) SATISFIES v.name = “user:1” AND v.val.device = “pc” END,
here the output should be {“login_name”:<user_list.usr:1.login_time>}

15 
    SELECT FIRST v.val.login_time FOR v IN OBJECT_PAIRS(user_list) WHEN  v.name = "user:1" AND v.val.device = "pc" END AS login_time 
   from default   WHERE ANY v IN OBJECT_PAIRS(user_list) SATISFIES v.name = "user:1" AND v.val.device = "pc" END;

OR

SELECT users[0].login_time FROM default
 LET users = ARRAY {v.name, v.val.login_time, v.val.device} FOR v IN OBJECT_PAIRS(user_list) WHEN v.name = "user:1" AND v.val.device = "pc" END
  WHERE ARRAY_LENGTH(users) > 0;
1 Like