Need a function or pseudo-field to return the number of elements in a BSON array

Put in simplest terms, if I have a JSON/BSON record with an embedded array, I need to know how many elements are in the array. I need a function similar to bson_size() (which returns the string length of the total array) which instead returns the number of elements in the array. Let's call it bson_array_count() that would look something like this (simulation):

create table bson_array( one serial, data bson );insert into bson_array values (1, '{id:1234,vals:[{fname:"fred",lname:"flintstone"},{fname:"barney",lname:"rubble"},{fname:"wilma",lname:"flintstone"}]}'::bson );

select bson_array_count( data, "vals" ) from bson_array where one = 1;

expression

MongoDB has a count() function for this and other JSON interfaces offer a length() function or pseudo-element. The latter might look like:

select data.vals.count from bson_array where one = 1;

How can we claim compatibility with the MongoDB API if we don't supply an equivalent to the count() function? And if it's available at the API layer through the wire protocol listener (I haven't seen it documented to be there) then why not implement it in the SQL layer?

Needed By

Yesterday (Let's go already!)

Post comment

Guest

Reply
| Feb 7, 2022

It turns out that there is such a function which is undocumented:
let nelem = bson_array_size( brec, 'array_fld' );

Where "brec" is the name of the BSON column and "array_fld" is the name of the array type field within that BSON column value as a quoted string (or the content of a host variable). Sample (using a literal JSON string cast to BSON):
> execute function bson_array_size( '{"totmiles":320,"state_miles":[{"state":"IL","miles":15},{"state":"IN","miles":152}]}'::json::bson, 'state_miles' );

(expression)

2

1 row(s) retrieved.

0 reply Hide replies

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

Need a function or pseudo-field to return the number of elements in a BSON array