MariaDB 10.3.21如何查询JSON中cb=1的父键数组

Got it, let's tackle this problem step by step. You're trying to extract all the contract type IDs (the parent keys) where the cb field is set to '1' from a JSON column in MariaDB 10.3.21, right? The tricky part here is that those parent keys are dynamic, so you can't hardcode paths like $.2.cb for every possible contract type.

Here's a solution that uses MariaDB's JSON_TABLE and JSON_KEYS functions to unpack the JSON object, filter the relevant entries, and aggregate the results into a JSON array:

SELECT 
    c.id,
    c.company,
    -- Use COALESCE to return an empty array instead of NULL if no active contracts exist
    COALESCE(JSON_ARRAYAGG(jt.contract_id), JSON_ARRAY()) AS active_contract_ids
FROM 
    db.clients c
-- First, get all the contract type IDs (parent keys) from the JSON object
JOIN 
    JSON_TABLE(
        JSON_KEYS(c.contracts),
        '$[*]' COLUMNS(contract_id VARCHAR(10) PATH '$')
    ) jt
-- Check if the corresponding cb field is '1'
WHERE 
    JSON_VALUE(c.contracts, CONCAT('$.', jt.contract_id, '.cb')) = '1'
-- Group by client to aggregate their active contract IDs
GROUP BY 
    c.id, c.company;

How this works: #

1. JSON_KEYS(c.contracts): Extracts all the top-level keys (your contract type IDs) from the contracts JSON object, returning them as a JSON array (e.g., ["1","2","3","7"] for your sample data).
2. JSON_TABLE(...): Converts that JSON array of keys into a relational table with a single column contract_id, so each key becomes a separate row.
3. JSON_VALUE(..., CONCAT('$.', jt.contract_id, '.cb')): Dynamically builds the path to the cb field for each contract type ID, then checks if its value is '1'.
4. JSON_ARRAYAGG(jt.contract_id): Aggregates all the matching contract type IDs back into a single JSON array (like ["2","7"] for your sample). The COALESCE ensures you get an empty array [] instead of NULL if a client has no active contracts.

Why your previous attempts didn't work: #

• Wildcard paths with JSON_VALUE: JSON_VALUE expects a specific path, and wildcards like $.*.cb aren't supported directly for extracting parent keys—you need to unpack the JSON first.
• JSON_SEARCH: This function searches for specific values and returns their paths, but it's not designed to retrieve the parent keys of matching nested values. Searching for "cb":"1" would look for that exact string in the JSON, which doesn't match the nested structure of your data.

Notes: #

• Make sure to use the string value '1' in the WHERE clause, since your sample JSON stores cb as a string (not a number).
• If your contracts column can be NULL, add c.contracts IS NOT NULL to the WHERE clause to avoid errors.

内容的提问来源于stack exchange，提问作者Yggy Drasil