Select from object two level down without knowing the keys

@vsr1

I have a quick question:

I have a document containing someting similar with:

        "level-1": {
          "level-2": {
            "A": {
              "cr": 483,
              "crt": 1618930800,
              "hr": 270,
              "hrt": 1618837200
            },
            "B": {
              "cr": 302,
              "crt": 1621947600,
              "hr": 35,
              "hrt": 1618869600
            },
            "C": {
              "cr": 351,
              "crt": 1621944000,
              "hr": 27,
              "hrt": 1618837200
            }
          }
        }

is there any way in which I can query the values of key A,B, or C, without knowing the key of level-1 and level-2 ? Something of just getting watever data is two level down for the key A

SELECT FIRST v.A FOR v WITHIN d WHEN v.A IS NOT MISSING END AS  A
FROM default AS d
WHERE ....;

SELECT ARRAY  v.A FOR v WITHIN d WHEN v.A IS NOT MISSING END AS  A
FROM default AS d
WHERE ....;

Thanks for the super fast answer…

Actually I tried something similar but now I realized that my problem is that in my case the A, B, C are numbers, and I tried with v.1 and v.“1” and it doesn’t seem to work. how do I correctly use numbers as keys?

SELECT FIRST v.`1234` FOR v WITHIN d WHEN v.`1234` IS NOT MISSING END AS  A
FROM default AS d
WHERE ....;

SELECT  ARRAY_FLATTEN(ARRAY  (ARRAY  v.[TO_STR(v1)]   FOR v1 IN [12345,6789] WHEN v.[TO_STR(v1)] NOT MISSING END) FOR v WITHIN d END,2) AS  A
FROM default AS d
WHERE ....;
1 Like

oh yes, I am so stupid :slight_smile: