Field Group searching is always OR cases. It has to do this because we have to search inside of all of the potential rows. Example of how the SQL comes out.
Code:
AND ((JSON_SEARCH( LOWER( ue.`cb_fieldgroup` ), 'one', '%test%', NULL, '$[*].cb_fieldgroup_text' ) IS NOT NULL OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[0].cb_fieldgroup_date' ) ) >= '1911-01-01'
AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[0].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[1].cb_fieldgroup_date' ) ) >= '1911-01-01'
AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[1].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[2].cb_fieldgroup_date' ) ) >= '1911-01-01'
AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[2].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[3].cb_fieldgroup_date' ) ) >= '1911-01-01'
AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[3].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[4].cb_fieldgroup_date' ) ) >= '1911-01-01'
AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[4].cb_fieldgroup_date' ) ) <= '1929-01-01')))
Notice how cb_fieldgroup_date is checked 5 times. This is because the field allows for 5 rows. We've to extract the date and compare the search value against each row individually since JSON_SEARCH can't do comparisons like this. If those were set to AND it'd never find a user.
Have added a feature ticket to look into supporting AND case again, but it's likely not possible with our current search behavior without crazy amounts of nesting that'll unoptimize the query even more. I don't recommend using field groups if you depend on searching their contents as it's inaccurate searching into JSON strings.
forge.joomlapolis.com/issues/8467
It becomes an even bigger problem when we optimize away JSON_EXTRACT in favor of JSON_SEARCH as we then can't separate the OR cases by row.
krileon
