How to select specific data in array

SQL++
1

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
2

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
3

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
4

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

5

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

2 Likes
6

Thanks, @geraldss

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

7

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
8

How do select the element from array that contains escape sequence.
Ex:
{
“REF_DATA”:"[{mtrCd:“A”,shrtMtrDesc:“pam”,longMtrDesc:“pam”,priNbr:“2”,updUserid:“12345”,odsCreateTmstmp:“2019-12-4 18:55:2. 0”,odsUpdTmstmp:“2019-12-4 18:55:2. 0”}}]",
“UPD_TMSTMP”:“16-Dec-2019 05.09.45.203”,
“CREATE_TMSTMP”:“16-Dec-2019 05.09.45.203”
}

9

@srikrishna4214,

With in the string you have array. That is considered as string. It is not an array. You can parse in the application. If you want it as array and perform array operations store it as array.

{
"REF_DATA":'[{"mtrCd":"A","shrtMtrDesc":"pam","longMtrDesc":"pam","priNbr":"2","updUserid":"12345","odsCreateTmstmp":"2019-12-4 18:55:2.0","odsUpdTmstmp":"2019-12-4 18:55:2.0"}]'
}

The array is string, which is encoded json. You can use DECODE_JSON(REF_DATA) which converts encoded JSON into actual JSON. After that you can use array syntax to access any info as described in https://blog.couchbase.com/working-json-arrays-n1ql/

1 Like