SQLize | PHPize | SQLtest

create table tbl ( aaa json); insert into tbl values [ { "aaa02": "開機檢查", "aaa03": "1", "options": [ { "aab02": "J031", "aab03": "正常開機", "aab04": "A", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J032", "aab03": "可開機、無法進到桌面", "aab04": "A", "aad02": "-999999", "aae01": "", "aac07": 29500 }, { "aab02": "J033", "aab03": "無法開機", "aab04": "A", "aad02": "-999999", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "IMEI / 序號", "aaa03": "4", "options": [ { "aab02": "J041", "aab03": "IMEI / 序號", "aab04": "D", "aad02": "0", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "iCloud 已解除", "aaa03": "1", "options": [ { "aab02": "J051", "aab03": "已解除", "aab04": "A", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J052", "aab03": "未解除", "aab04": "A", "aad02": "-999999", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "外觀檢查", "aaa03": "1", "options": [ { "aab02": "J061", "aab03": "A級：少用，外觀無傷", "aab04": "B", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J062", "aab03": "B級：常用，外觀有輕微使用痕跡", "aab04": "B", "aad02": "-10", "aae01": "", "aac07": 29500 }, { "aab02": "J063", "aab03": "C級：外觀有明顯凹傷、敲傷", "aab04": "B", "aad02": "-25", "aae01": "", "aac07": 29500 }, { "aab02": "J064", "aab03": "D級：外觀變形、彎曲、破損", "aab04": "B", "aad02": "-85", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "螢幕檢查", "aaa03": "1", "options": [ { "aab02": "J071", "aab03": "顯示正常，無刮傷", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J072", "aab03": "顯示正常，輕微刮傷", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J073", "aab03": "顯示正常但有亮點、暗點、亮塊、暗塊、暗角、老化泛黃", "aab04": "C", "aad02": "-700", "aae01": "", "aac07": 29500 }, { "aab02": "J074", "aab03": "顯示或觸控異常，如螢幕破裂、漏液、烙印、色偏、亮線、暗線", "aab04": "C", "aad02": "-1400", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "電源鍵功能", "aaa03": "1", "options": [ { "aab02": "J081", "aab03": "正常", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J082", "aab03": "異常", "aab04": "C", "aad02": "-1000", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "靜音鍵功能", "aaa03": "1", "options": [ { "aab02": "J091", "aab03": "正常", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J092", "aab03": "異常", "aab04": "C", "aad02": "-1000", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "音量鍵功能", "aaa03": "1", "options": [ { "aab02": "J101", "aab03": "正常", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J102", "aab03": "異常", "aab04": "C", "aad02": "-1000", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "Home鍵 / 指紋辨識功能", "aaa03": "1", "options": [ { "aab02": "J111", "aab03": "正常", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J112", "aab03": "異常", "aab04": "C", "aad02": "-1800", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "電池續電功能", "aaa03": "1", "options": [ { "aab02": "J121", "aab03": "正常 80%以上", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J122", "aab03": "異常 低於80%", "aab04": "C", "aad02": "-900", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "電池續電功能", "aaa03": "1", "options": [ { "aab02": "J131", "aab03": "正常", "aab04": "C", "aad02": "0", "aae01": " ", "aac07": 29500 }, { "aab02": "J132", "aab03": "異常", "aab04": "C", "aad02": "-1000", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "WiFi / 藍芽功能", "aaa03": "1", "options": [ { "aab02": "J141", "aab03": "正常", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J142", "aab03": "異常", "aab04": "C", "aad02": "-2000", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "後相機鏡頭功能", "aaa03": "1", "options": [ { "aab02": "J151", "aab03": "正常", "aab04": "C", "aad02": "0", "aae01": "", "aac07": 29500 }, { "aab02": "J152", "aab03": "異常", "aab04": "C", "aad02": "-1000", "aae01": "", "aac07": 29500 } ] }, { "aaa02": "其他功能檢查", "aaa03": "2", "options": [ { "aab02": "J161", "aab03": "充電功能異常", "aab04": "C", "aad02": "-2000", "aae01": "", "aac07": 29500 }, { "aab02": "J162", "aab03": "接近感應器（距離感應器）功能異常", "aab04": "C", "aad02": "-800", "aae01": "", "aac07": 29500 }, { "aab02": "J163", "aab03": "光感應功能異常", "aab04": "C", "aad02": "-800", "aae01": "", "aac07": 29500 }, { "aab02": "J164", "aab03": "方向感應器功能異常", "aab04": "C", "aad02": "-2000", "aae01": "", "aac07": 29500 }, { "aab02": "J165", "aab03": "壓力計功能異常", "aab04": "C", "aad02": "-2000", "aae01": "", "aac07": 29500 }, { "aab02": "J166", "aab03": "陀螺儀功能異常", "aab04": "C", "aad02": "-2000", "aae01": "", "aac07": 29500 }, { "aab02": "J167", "aab03": "電子羅盤功能異常", "aab04": "C", "aad02": "-2000", "aae01": "", "aac07": 29500 }, { "aab02": "J168", "aab03": "手電筒功能異常", "aab04": "C", "aad02": "-1000", "aae01": "", "aac07": 29500 }, { "aab02": "J169", "aab03": "定位功能異常", "aab04": "C", "aad02": "-2000", "aae01": "", "aac07": 29500 }, { "aab02": "J17A", "aab03": "喇叭功能異常或破音", "aab04": "C", "aad02": "-900", "aae01": "", "aac07": 29500 }, { "aab02": "J17B", "aab03": "聽筒功能異常或破音", "aab04": "C", "aad02": "-900", "aae01": "", "aac07": 29500 }, { "aab02": "J17C", "aab03": "耳機孔功能異常", "aab04": "C", "aad02": "-1000", "aae01": "", "aac07": 29500 } ] } ]; select * from tbl where status = 3 and json_contains(home, '10', '$.id')