Is it possible to combine multiple docs into one doc using n1ql?

SQL++
1

Hello,

We have an api that queries 15~30 docs at a time with n1ql, which is very fast.

While benchmarking the api, we’ve found that json_decode takes pretty good amount of CPU % in PHP application.

I manually combined all of them into one single document and tested the api again. It is much faster. I suspect that decoding one big doc is faster than decoding multiple smaller docs.

Is there a way to put multiple docs into an array so that the result gets treated as if it was a single doc in a language?

2

Hi @moon0326,

Can you share queries? The answer to your question should be yes.

Ideally, we can avoid the JSON_DECODE() altogether.

3

I’m using this query to query the docs

select bucket.* from bucket where form='value'

Thank you!

4

Ok, so you are talking about JSON decode in your client, and not the N1QL function JSON_DECODE.

You can combine your results as follows.

select array_agg(bucket) as list from bucket where form='value';

5

Thank you so much. That’s exactly what I wanted.

Is there a way to use ‘order by’ in this case?

When I add ‘order by id asc’ at the end, I get the following error messages.

Expression must be a group key or aggregate:

6

To sort by field sort_field, you can do this.

select array_sort( array_agg( [ b.sort_field, b ] ) ) as list from bucket b where form='value';

7

That’s exactly what I wanted.

Thank you so much!

1 Like