sqljsonnosqlcouchbasesql++

Couchbase n1q1 query to fetch map's specific key


Below is the couchbase doc `

{
  "docId": "1111212",
  "user": {
    "id": "5050017",  
    "name": "string", 
    "type": "string"
  },
  "books": {
        "1": {
          "id": "1",
          "name": "Book1",
          "author":{
             "id":"A1",
             "name":"name"
          }
        }
      "2": {
          "id": "2",
          "name": "Book2",
          "author":{
             "id":"A2",
             "name":"name"
            }
        }
    }
}

  

` I need to fetch all Books name using select query I tried this

SELECT b.books.*.name FROM bucket_name b   where meta().id like '%books_details%' OFFSET 0 LIMIT 10

I am able to get all json keys under books but not the field under each map.

SELECT OBJECT_NAMES(b.books) FROM bucket_name b where meta().id like '%books_details%' OFFSET 0 LIMIT 10


Solution

  • SELECT u.* 
    FROM bucket_name AS b 
    UNNEST OBJECT_VALUES(b.books) AS u
    WHERE .....;
    
    SELECT ARRAY v.name FOR f:v IN b.books END AS names
    FROM bucket_name AS b;