sql - 删除 JSON_AGG（聚合函数）中的括号和引号

Question

public function fetchdrug(Request $search_drug){

    $filter_drug = $search_drug->input('search_drug');
    $all_drugs = HmsBbrKnowledgebaseDrug::selectRaw('DISTINCT ON (drug_code)
                                                    drug_code,
                                                    drug_name,
                                                    JSON_AGG(drug_dosage) AS dosage_list')
                                ->GroupBy('drug_code', 'drug_name')
                                ->orderBy('drug_code', 'ASC')
                                ->get();

    return response()->json([
        'all_drugs'=>$all_drugs,
    ]);
}

我正在使用JSON_AGG检索多行drug_dosage并将它们组合成一个，但是我在输出中得到一个括号和引号，我该如何取出它？

更新：我在示例中遇到错误，因为我正在尝试使用str_replaceand的解决方案preg_replace。我的问题是目标在 SQL 语句中，所以我怀疑这与错误有关，因为结果错误中还有其他数据：

  Uncaught TypeError: Cannot use 'in' operator to search for 'length' in 
{"drug_code":"CFZU",
 "drug_name":"Cefazolin",
 "dosage_list":"[\"<=4 mg\/L\", \"<=3 mg\/L\"]"}, 
{"drug_code":"TZPD","drug_name":"Pip\/Tazobactam",
 "dosage_list":"[\"Pip\/Tazobactam\"]"}

标签： sqllaravelfunctioncontrolleraggregate-functions

Answer 1

你可以string_agg试试JSON_AGG

public function fetchdrug(Request $search_drug){

$filter_drug = $search_drug->input('search_drug');
$all_drugs = HmsBbrKnowledgebaseDrug::selectRaw('DISTINCT ON (drug_code)
                                                drug_code,
                                                drug_name,
                                                string_agg(drug_dosage, ', ') AS dosage_list')
                            ->GroupBy('drug_code', 'drug_name')
                            ->orderBy('drug_code', 'ASC')
                            ->get();

return response()->json([
    'all_drugs'=>$all_drugs,
]);

}