SQL query advice for extracting data in orientdb

Question

I'm struggling with a SQL query to extract data from OrientDB.

Assuming, that each document holds information on each person attendance in different days in month I need to check which persons are present on certain day in month.

Document structure (attendance):

{
  "2017": {
    "10": [
      {
        "day": "2",
        "presence": "1"
      },
      {
        "day": "3",
        "presence": "1"
      },
      {
        "day": "4",
        "presence": "0"
      },
      {
        "day": "5",
        "presence": "1"
      },
      {
        "day": "6",
        "presence": "1"
      },
      {
        "day": "9",
        "presence": "0"
      },
      {
        "day": "10",
        "presence": "1"
      },
      {
        "day": "11",
        "presence": "1"
      },
      {
        "day": "12",
        "presence": "1"
      },
      {
        "day": "13",
        "presence": "1"
      },
      {
        "day": "16",
        "presence": 1
      },
      {
        "day": "17",
        "presence": "0"
      },
      {
        "day": "18",
        "presence": 1
      },
      {
        "day": "19",
        "presence": 1
      },
      {
        "day": "20",
        "presence": 1
      },
      {
        "day": "23",
        "presence": "1"
      },
      {
        "day": "24",
        "presence": "0"
      },
      {
        "day": "25",
        "presence": "1"
      },
      {
        "day": "26",
        "presence": "1"
      },
      {
        "day": "27",
        "presence": "0"
      },
      {
        "day": "30",
        "presence": "1"
      },
      {
        "day": "31",
        "presence": "1"
      }
    ],
    "09": [
      {
        "day": "1",
        "presence": "0"
      },
      {
        "day": "4",
        "presence": "0"
      },
      {
        "day": "5",
        "presence": "0"
      },
      {
        "day": "6",
        "presence": "0"
      },
      {
        "day": "7",
        "presence": "1"
      },
      {
        "day": "8",
        "presence": "1"
      },
      {
        "day": "11",
        "presence": "1"
      },
      {
        "day": "12",
        "presence": "1"
      },
      {
        "day": "13",
        "presence": "1"
      },
      {
        "day": "14",
        "presence": "1"
      },
      {
        "day": "15",
        "presence": "0"
      },
      {
        "day": "18",
        "presence": "1"
      },
      {
        "day": "19",
        "presence": "1"
      },
      {
        "day": "20",
        "presence": "0"
      },
      {
        "day": "21",
        "presence": "1"
      },
      {
        "day": "22",
        "presence": "1"
      },
      {
        "day": "25",
        "presence": "1"
      },
      {
        "day": "26",
        "presence": "1"
      },
      {
        "day": "27",
        "presence": "0"
      },
      {
        "day": "28",
        "presence": "1"
      },
      {
        "day": "29",
        "presence": "1"
      }
    ]
  },
  "person_id": "0f805072-d453-4321-8047-1f75bcb96451"
}

What I want to get sort of:

"select person_id, presence from attendance[2017][10] where day = 31 and presence = 1"

but I can't make it work in orientDB..

Maybe, for some reason data structure is sh*tty - please advise.

Thanks, Rafal

Answer 1

Try this:

select 
   person_id, 
   attendance["2017"]["10"][day = "31" AND presence = "1"] as presence 
from attendance 
UNWIND presence

Please notice that I quoted 2017, 10, 31 and 1 because in your model they seem to be strigs, if they are numbers you can remove the quotes

Answer 2

I got it!

For example:

select person_id from attendance where 2017['10'] contains (day = 222 and presence=1)

That does the trick.

Actually reading the documentation helped: http://orientdb.com/docs/1.7.8/orientdb.wiki/SQL-Where.html

contains operator can match if the given collection contains at least one element that satisfy the next condition. Simple as that.