Hi,

I'm testing BSON on Informix 15.

I have the following table:
 create table brev (
   seq serial not null ,
   data "informix".bson )
   PUT data in ( sbspace1 ) extent size 10000 next size 10000 lock mode row ;

 This is an example of one row:
rseq 824
rdata {"_id":ObjectId("683f199109fee14900003002"),"rv1_cv1_seq":0,"r v1_fact":"02816","rv1_cl1_code":"ANDREROY","rv1_date":ISODate( "2023-04-20T00:00:00.000Z"),"rv1_heure":"10:43","rv1_dev":"t1w1","rv1_user":"gestion","rv1_em1_code":"CHARJEA2","rv1_en1_code":"1","rv1_com_cli":"","rv1_code_tx_prov":"O","rv1_lic_prov": "","rv1_code_tx_fed":"O","rv1_taux_esc":0,"rv1_livr":"N","rv1_livr_nom":"","rv1_livr_rue":"","rv1_livr_ville":"","rv1_livr_prov":"","rv1_livr_tel":"","rv1_livr_cp":"","rv1_mnt_brut":56,"rv1_mnt_esc":0,"rv1_mnt_tx_prov":2.39,"rv1_mnt_tx_fed":3.92,"r v1_mnt":62.31,"rv1_mnt_paye":62.31,"rv1_ech":ISODate("1993-05- 20T00:00:00.000Z"),"rv1_ech_esc":ISODate("1993-04-20T00:00:00. 000Z"),"rv1_taux_esc_term":0,"rv1_jg1_seq":815,"rv1_seq":824," rv1_no_harvest":"","rev2s":[{"rv2_cat":"M","rv2_in1_code":"ANDREROY-8649","rv2_code_prix":"1","rv2_code_esc":"0","rv2_code_t x_prov":"O","rv2_code_tx_fed":"O","rv2_desc":"LISTE DES RX POU R UN DIN ENTRE","rv2_qte":0.5,"rv2_unit_vente":"HRE","rv2_pri x_unit":80,"rv2_cout":0,"rv2_mnt_brut":40,"rv2_mnt":44.51,"rv2 _taux_esc":0,"rv2_mnt_esc_lig":0,"rv2_mnt_esc_fact":0,"rv2_tau x_tx_prov":4,"rv2_mnt_tx_prov":1.71,"rv2_taux_tx_fed":7,"rv2_m nt_tx_fed":2.8,"rv2_co1_fol":"4020","rv2_rv1_seq":824,"rv2_seq ":2529,"rv2_date":ISODate("1993-04-13T00:00:00.000Z")},{"rv2_c at":"T","rv2_in1_code":"","rv2_code_prix":"","rv2_code_esc":"" ,"rv2_code_tx_prov":"","rv2_code_tx_fed":"","rv2_desc":"2 DATE S","rv2_qte":0,"rv2_unit_vente":"","rv2_prix_unit":0,"rv2_cout ":0,"rv2_mnt_brut":0,"rv2_mnt":0,"rv2_taux_esc":0,"rv2_mnt_esc _lig":0,"rv2_mnt_esc_fact":0,"rv2_taux_tx_prov":4,"rv2_mnt_tx_ prov":0,"rv2_taux_tx_fed":7,"rv2_mnt_tx_fed":0,"rv2_co1_fol":" ","rv2_rv1_seq":824,"rv2_seq":2530,"rv2_date":ISODate("1993-04 -20T00:00:00.000Z")},{"rv2_cat":"M","rv2_in1_code":"ANDREROY8651","rv2_code_prix":"1","rv2_code_esc":"0","rv2_code_tx_prov" :"O","rv2_code_tx_fed":"O","rv2_desc":"FRAIS DE MODEM","rv2_qt e":0.5,"rv2_unit_vente":"HRE","rv2_prix_unit":32,"rv2_cout":0, "rv2_mnt_brut":16,"rv2_mnt":17.8,"rv2_taux_esc":0,"rv2_mnt_esc _lig":0,"rv2_mnt_esc_fact":0,"rv2_taux_tx_prov":4,"rv2_mnt_tx_ prov":0.68,"rv2_taux_tx_fed":7,"rv2_mnt_tx_fed":1.12,"rv2_co1_ fol":"4300","rv2_rv1_seq":824,"rv2_seq":2531,"rv2_date":ISODat e("1993-04-13T00:00:00.000Z")}]}

How to return the list of values in the column rv2_in1_code of array rev2s ?

I try with BSON_GET and BSON_VALUE_VARCHAR with no luck.
The only way I found is to access the values one by one:

select
    BSON_VALUE_VARCHAR(BSON_GET(brev.data, "rev2s.0"), "rev2s.rv2_in1_code") code0,
    BSON_VALUE_VARCHAR(BSON_GET(brev.data, "rev2s.1"), "rev2s.rv2_in1_code") code1,
    BSON_VALUE_VARCHAR(BSON_GET(brev.data, "rev2s.2"), "rev2s.rv2_in1_code") code2
FROM
    brev
WHERE
    seq = 824;

code0              ANDREROY-8649
code1
code2              ANDREROY-8651

What I need is to join these values to another table and the number of entry in the rev2s array is variable.
So one by one is not a solution.

Somebody used BSON and have a solution ?

Best Regards,