Select JSON object that appears more than one time

I am trying to write a query to return all trains that have more than one etapesSupervision.

My table has a column called DETAIL, in this column I can find the JSON of my train.

"nomTrain": "EVOL99",
"compositionCourtLong": "LONG",

"sillons": [{

    "numeroTrain": "EVOL99"
}],
"sillonsV4": [{
"refSillon": "sillons/4289505/2"
}],
"branchesStif": [{
    "data": "49",
    "data": "BP",
    "data": "ORIGINE"
} ],
"etapesSupervision": [{
    "data": "PR/0087-758896-00",
    "data": "PR/0087-758607-BV",
    "superviseur": "1287",
    "uoSuperviseur": "B"
},
{
    "data": "PR/0087-758607-BV",
    "data": "PR/0087-001479-BV",
    "superviseur": "1287",
    "uoSuperviseur": "B"
}],

This is the query I wrote :

    select * from course where CODE_LIGNE_COMMERCIALE='B'
     --and ref = 'train/2018-11-12'
    and  instr(count(train.detail,'"etapesSupervision":'))> 1 ;

Using this, I return trains with only one etapesSupervision.

The thing is the column DETAIL is JSON, so I feel like I can’t do a lot with it.

I tried also with like, but it doesn’t work either.

Answer

Thank you for your comments. This is the query that worked:

select data,data,data
from train
where 
 length(DETAIL) - length(replace(DETAIL,'uoSuperviseur',null)) > 20  ;

And this way I have only trains that have more than one supervisor.

Thanks again

Advertisement

Answer