Also function range(x, y)
<https://ci.apache.org/projects/asterixdb/sqlpp/builtins.html#MiscFunctions>
can
be helpful. Here's a function *zipWithIndex *which does exactly what you
need:
DROP FUNCTION zipWithIndex@1 IF EXISTS;
CREATE FUNCTION zipWithIndex(array_of_objects) {
SELECT object_merge(array_of_objects[i-1], {"i": i})
FROM range(1, array_length(array_of_objects)) as i
};
SELECT value zipWithIndex(x.B)
FROM root as x;
Output:
[
1. <http://192.168.0.100:8080/>[
1. <http://192.168.0.100:8080/>{
- $1 <http://192.168.0.100:8080/>: {
- B1: "b111",
- B2: "b112",
- i: 1
}
},
2. <http://192.168.0.100:8080/>{
- $1 <http://192.168.0.100:8080/>: {
- B1: "b121",
- B2: "b122",
- i: 2
}
}
],
2. <http://192.168.0.100:8080/>[
1. <http://192.168.0.100:8080/>{
- $1 <http://192.168.0.100:8080/>: {
- B1: "b211",
- B2: "b212",
- i: 1
}
},
2. <http://192.168.0.100:8080/>{
- $1 <http://192.168.0.100:8080/>: {
- B1: "b221",
- B2: "b222",
- i: 2
}
}
],
3. <http://192.168.0.100:8080/>[
1. <http://192.168.0.100:8080/>{
- $1 <http://192.168.0.100:8080/>: {
- B1: "b311",
- B2: "b312",
- i: 1
}
},
2. <http://192.168.0.100:8080/>{
- $1 <http://192.168.0.100:8080/>: {
- B1: "b321",
- B2: "b322",
- i: 2
}
}
]
]
On Tue, Aug 20, 2019 at 3:07 PM Michael Carey <[email protected]> wrote:
> See if the new window function support (see the OVER clause in the SQL++
> Language documentation and the Window Functions section of the Built-in
> Functions documentation for more info) meets your needs? The rank or
> dense_rank functionality might do the trick.... (Eventually SQL++ will
> have positional variable support, but right now it does not; that's in
> progress, as it's slightly tricky in a shared-nothing parallel setting.)
> On 8/16/19 2:53 AM, [email protected] wrote:
>
> Hello everyone, apologies if this is a trivial question.
>
> I am trying to do something like this:
>
> ================================
> DROP DATAVERSE test IF EXISTS;
> CREATE DATAVERSE test;
> USE test;
>
> CREATE TYPE B AS {
> B1: string,
> B2: string
> };
>
> CREATE TYPE RooTType As{
> id:uuid,
> A: string,
> B:[B]
> };
>
> CREATE DATASET root (RooTType) PRIMARY KEY id AUTOGENERATED;
>
> INSERT INTO root([
> {
> "A": "a1",
> "B": [{
> "B1": "b111",
> "B2": "b112"
> },
> {
> "B1": "b121",
> "B2": "b122"
> }]
> },
> {
> "A": "a2",
> "B": [{
> "B1": "b211",
> "B2": "b212"
> },
> {
> "B1": "b221",
> "B2": "b222"
> }]
> },
> {
> "A": "a3",
> "B": [{
> "B1": "b311",
> "B2": "b312"
> },
> {
> "B1": "b321",
> "B2": "b322"
> }]
> }
> ]);
>
> FROM root, root.B as B
> SELECT root.A, (
> FROM B
> LET I = I + 1
> SELECT B.B1, B.B2, I
> ) AS B;
>
> ================================
>
> Basically I would like I to be an index of the occurrence of B being
> produced. Would be value 1 or 2.
>
> Output should look like this:
>
> [ { "A": "a1", "B": [ { "B1": "b111", "B2": "b112", "I": 1 }, { "B1":
> "b121", "B2": "b122", "I": 2 } ] }
> , { "A": "a2", "B": [ { "B1": "b211", "B2": "b212", "I": 1 }, { "B1":
> "b221", "B2": "b222", "I": 2 } ] }
> , { "A": "a3", "B": [ { "B1": "b311", "B2": "b312", "I": 1 }, { "B1":
> "b321", "B2": "b322", "I": 2 } ] }
> ]
>
> Is there a way to do that?
>
> Thank you!
>
> Fady
>
>
--
*Regards,*
Wail Alkowaileet
