Hot to join with array of objects

N1QL
1

this is sample data

{
  "type": "book",
  "name": "Harry Poter",
  "author": "Joanne Rowling",
  "storeId": "aaa"
}

{
  "type": "book",
  "name": "The hobbit",
  "author": "John Ronald Reuel Tolkien",
  "storeId": "aaa"
}

{
  "type": "book",
  "name": "Lord of the rings book",
  "author": "John Ronald Reuel Tolkien",
  "storeId": "bbb"
}

{
  "type": "store",
  "name": "Jam's book store",
  "storeId": "aaa",
  "address": "A st."
}

{
  "type": "store",
  "name": "Kevin's book store",
  "storeId": "bbb",
  "address": "B st."
}

I can’t figure out hot to make query that includes books array object.

[
  {
    "address": "A st.",
    "books": [
      {
        "author": "Joanne Rowling",
        "name": "Harry Poter",
        "storeId": "aaa",
        "type": "book"
      },
      {
        "author": "John Ronald Reuel Tolkien",
        "name": "The hobbit",
        "storeId": "aaa",
        "type": "book"
      }
    ],
    "name": "Jam's book store",
    "storeId": "aaa",
    "type": "store"
  },
  {
    "address": "B st.",
    "books": [
      {
          "type": "book",
          "name": "Lord of the rings book",
          "author": "John Ronald Reuel Tolkien",
          "storeId": "bbb"
        }
    ],
    "name": "Kevin's book store",
    "storeId": "bbb",
    "type": "store"
  }
]

I tried to this query, but result is not collect…

select store.*, (select book.* from default as book WHERE book.type="book" and book.storeId = (select raw storeId from store.storeId as storeId )[0]) as books
from default as store
where store.type="store"

what’s wrong??

thanks…

thanks.

2 
CREATE INDEX ix1 ON default(storeId) WHERE type = "book";
SELECT s.*, b AS books
FROM default AS s
LEFT NEST default AS b ON  s.storeId = b.storeId AND b.type = "book"
WHERE s.type = "store"
1 Like