JSON Query with Laravel

JSON_Query.txt

// Sample Query

$page = Page::query()
            ->select('title',\DB::raw('advance_custom_fields->"$[*].field_type" AS field'))
            ->whereRaw('advance_custom_fields->"$[*].field_type" IS NOT NULL')
            ->whereRaw('JSON_SEARCH(advance_custom_fields, "one", "image") IS NOT NULL')
            ->get();
            
Table is pages
json coloumn is advance_custom_fields

Example json
[
	{
		"name": "",
		"style": "col-md-4 order-first",
		"attributes": {
			"val_1": "aa",
			"val_2": "dd",
			"bg_color": "",
			"field-css": "col-md-4 order-first",
			"border_color": ""
		},
		"field_type": "image"
	},
	{
		"name": "",
		"style": "",
		"attributes": {
			"val_1": [
				"/uploads/1/A.png",
				"/uploads/2/B.png"
			],
			"bg_color": "",
			"field-css": "",
			"border_color": ""
		},
		"field_type": "carousel"
	}
]


$[*].field_type
$ -> json
[*] -> array
field_type -> key




//Select            
->select('title', \DB::raw('advance_custom_fields->"$[*].field_type" AS field'))

or

removing double quote from result with 'JSON_UNQUOTE'
->select('title', \DB::raw('JSON_UNQUOTE(advance_custom_fields->"$[*].field_type") AS field'))

removing double quote from result short version with '->>'
->select('title', \DB::raw('advance_custom_fields->>"$[*].field_type" AS field'))

//whereRaw
->whereRaw('advance_custom_fields->"$[*].field_type" IS NOT NULL')
or
->whereRaw('JSON_SEARCH(advance_custom_fields, "all", "image") IS NOT NULL')