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
