Flatten/nesting an object

SQL++
1

I tried to get the max of a nested object without any succes.

the document looks like this:

 "r": {
      "a": {
        "aa": {
          "bb": {
            "3": {
              "cr": 478,
              "crt": 1611468000,
              "hr": 471,
              "hrt": 1611460800
            },
            "14": {
              "cr": 449,
              "crt": 1611482400,
              "hr": 419,
              "hrt": 1611460800
            }
          }
        }
      },
      "b": {
        "aa": {
          "cc": {
            "3": {
              "cr": 445,
              "crt": 1612364400,
              "hr": 210,
              "hrt": 1611464400
            },
            "14": {
              "cr": 468,
              "crt": 1611522000,
              "hr": 244,
              "hrt": 1611460800
            }
          }
        }
      }
}

What I am trying to do is to get the MAC of all these values for the field “hr”, For some reason, I could not flatten or unnest these objects.

In addition, would be great to be able to count the number of times we have the field “hrt” with the value between x and y where the “hr” is larger/smaller than a certain value.

Could this be done in a N1QL query?

2

You need to know the path to reach a field.

See if this works for you

SELECT MAX(ARRAY_MAX( ARRAY v.hr FOR v WITHIN {d} WHEN v.hr IS NOT NULL END)) AS max,
       SUM( ARRAY_COUNT(ARRAY 1 FOR v WITHIN {d} WHEN v.hrt  BETWEEN 10 AND 20 AND (v.hr  < 50 OR v.hr > 1000) END) AS cnt
FROM default AS d;
1 Like
3

woau. you are fantastic!

4

@vsr1 do you think it is possible to also get the path somehow?

so if this

MAX(ARRAY_MAX( ARRAY v.hr FOR v WITHIN {d} WHEN v.hr IS NOT NULL END)) AS max

get the maximum from let’s say the first object, to also get the path to that, like in this case if 471 is the highest rank, the path would be: " r - a - aa - bb - 3" . Could this be possible, or at least a part from it?( I think I would be interested in the “bb” part)

    "r": {
          "a": {
            "aa": {
              "bb": {
                "3": {
                  "cr": 478,
                  "crt": 1611468000,
                  "hr": 471,
                  "hrt": 1611460800
                },
                "14": {
                  "cr": 449,
                  "crt": 1611482400,
                  "hr": 419,
                  "hrt": 1611460800
                }
              }
            }
          },
          "b": {
            "aa": {
              "cc": {
                "3": {
                  "cr": 445,
                  "crt": 1612364400,
                  "hr": 210,
                  "hrt": 1611464400
                },
                "14": {
                  "cr": 468,
                  "crt": 1611522000,
                  "hr": 244,
                  "hrt": 1611460800
                }
              }
            }
          }
    }
5

Unfortunately it is not possible to construct the path at present.

6

@vsr1,

Regarding this path,

do you think would be possible to get the path if I now the value of hr?

like for example if the hr is 244 to get something like b - aa - cc - 14? or at least just this array?

The reason I am asking this is that these arrays tend to be very big, like thousands of entries (same depth) and to get these and then iterate through them is a waste of bandwidth and CPU power.

Now, because I have the min of hr, I could try somehow to use that to identify the path. What do you think?

Also, one more question, regarding your recommendation query: MAX(ARRAY_MAX( ARRAY v.hr FOR v WITHIN {d} WHEN v.hr IS NOT NULL END)) AS max

do I still need to use the MAX in front of ARRAY_MAX, or is enough that I have the ARRAY_MAX?

7

At present there is no way to generate the path