N1QL with array

SQL++
1

Hello,

I need a little bit of help with a query.

I have an document which contains something like:

      "chart_a": {
        "1000008721": 5,
        "403274099": 2,
        "432756187": 1,
        "454403780": 3,
        "463569251": 4,
        "493073807": 7,
        "493837285": 6,
        "501344243": 8
 }

I have other docuemnts with the key something like a:a2t:{key of the above document array (chart_a)}
This objejct has a structure similar to:

Document key - a:a2t:432756187
{
“id”: 432756187,
“uuid”: “8067ae4f-59d9-497c-adf0-d87b8ecb654f”,
}

Document key - a:a2t:403274099
{
“id”: 403274099,
“uuid”: “4a20c2a9-8e36-4b7d-b961-e403d4a3cf5f”,
}

Would it be possible to make a select and produce a result similar to something like this ?

"chart": {
   1: {
        "uuid": "4a20c2a9-8e36-4b7d-b961-e403d4a3cf5f",
         "id":  432756187
       },
   2 : {
       "uuid": "4a20c2a9-8e36-4b7d-b961-e403d4a3cf5f",
         "id": 403274099
       },
  ETC..
}

@vsr1 Do you think you could do some magic?

2

Hi @flaviu,

Assumed chart_a is document key

SELECT OBJECT d.[TO_STR(v1.id)]:v1   FOR v1 IN av END AS chart
FROM default AS d USE KEYS "chart_a"
 LET  av =  (SELECT RAW d2 FROM default AS d2 USE KEYS (ARRAY  "a:a2t" || v FOR v IN OBJECT_NAMES(d) END));
3

Thans @vsr1,

You are very smart, that’s a briliant idea,

The problem I have is that the key of the resulting object should be the value of the initial object (chart_a).
And I just can’t mkae it work no matter what I do :((

The initial document is somethink like this:

{
  "country_code": "ae",
  "chart_timestamp": 1597536000,
  "chart_a": {
       "1000008721": 5,
        "403274099": 2,
        "432756187": 1,
        "454403780": 3,
        "463569251": 4,
        "493073807": 7,
        "493837285": 6,
        "501344243": 8
},
  "version": 1
}

the result should be something like this:

“chart”: {
1: {
“uuid”: “4a20c2a9-8e36-4b7d-b961-e403d4a3cf5f”,
“id”: 432756187
},
2 : {
“uuid”: “4a20c2a9-8e36-4b7d-b961-e403d4a3cf5f”,
“id”: 403274099
},
ETC…
}

where the keys 1,2,etc are the values of the initial document.

So, I need to get the values of the object from the initial document, transform in keys and then add the result from another documents as values to these keys

4

Hi @flaviu,

INSERT INTO default VALUES ("ch01", { "country_code": "ae", "chart_timestamp": 1597536000, "chart_a": { "1000008721": 5, "403274099": 2, "432756187": 1, "454403780": 3, "463569251": 4, "493073807": 7, "493837285": 6, "501344243": 8 }, "version": 1 });
INSERT INTO default VALUES ("a:a2t:432756187", { "id": 432756187, "uuid": "8067ae4f-59d9-497c-adf0-d87b8ecb654f" });
INSERT INTO default VALUES ("a:a2t:403274099", { "id": 403274099, "uuid": "4a20c2a9-8e36-4b7d-b961-e403d4a3cf5f" });

SELECT OBJECT TO_STR(d.chart_a.[TO_STR(v1.id)]):v1
       FOR v1 IN (SELECT RAW d2 FROM default AS d2 USE KEYS (ARRAY  "a:a2t:" || v FOR v IN OBJECT_NAMES(d.chart_a) END))
       END AS chart
FROM default AS d
WHERE d.country_code = "ae";
5

@vsr1 you are fantastic. Period!