How to select specific data in array

Here is a simple document

Bucket : BOOK    
doc id: book01
    {
    "title": "adventure",
    "custom": [
    	{"tag": "a01","price": 99},
    	{"tag": "b01","price": 99},
    	{"tag": "c01","price": 99}
    	]
    }

How to use N1QL to select the data, {“tag”: “b01”,“price”: 99} ?

The only thing I can do is to
SELECT custom[1] FROM BOOK WHERE title=‘adventure’

But the order maybe random and the tag value is not in pattern.

1 Like

Hey @agau9527,

Have you thought about using an UNNEST statement to flatten the array?

SELECT custom_data.tag, custom_data.price
FROM `bucket-name-here` AS books
UNNEST books.custom AS custom_data
WHERE title = 'adventure';

This of course is assuming I understood your question correctly :smile:

Let me know how it goes!

Best,

1 Like

Please try this:

SELECT FIRST t FOR t IN custom WHEN t.tag = “b01” END
FROM BOOK
WHERE ANY t IN custom SATISFIES t.tag = “b01” END;

1 Like

Thanks, @nraboy

But it returns parsing error, and @geraldss solve my problem!

All I want is only one record , {“tag”: “b01”,“price”: 99}

Thanks again, @geraldss

@nraboy’s solution works as below, and you may find it cleaner:

2 Likes

Thanks, @geraldss

It works fine! But which one is better in efficiency(search speed)?

The performance is similar. The UNNEST version will return multiple results if your custom array contains tag “b01” more than once. For both queries, you can create an index on title, and include title in the WHERE clause.

1 Like