JSON数组类型视图条件查询无结果？求数据获取解决方案

Let's break down what's going wrong with your current setup, then walk through the correct approaches to get the data you need.

Root Causes of the Issue #

Your query isn't returning results for two key reasons:

1. You're converting JSON to strings with concat()
   Wrapping json_array() in concat() turns the valid JSON array into a plain string. JSON functions like json_extract() won't work correctly on string values unless you explicitly cast them back to JSON—this extra step is unnecessary and error-prone.

2. Your JSON structure is a flat array, not key-value objects
   The array you created (["eid", id, "empname", ename]) is a list of alternating keys and values, not a proper JSON object. The path $[*].empname assumes you're dealing with an array of objects that have an empname property—this doesn't match your flat array structure at all.

Solution 1: Use JSON Objects (Matches Your Expected Output) #

Since your desired output is JSON objects, you should build your view with json_object() instead of json_array(). This creates clean, queryable key-value pairs.

Step 1: Fix the View Creation #

CREATE VIEW vw_json AS
SELECT 
  json_object('eid', id, 'empname', ename) AS a,
  json_object('esal', sal, 'edeptno', deptno) AS emp
FROM employe;

This creates columns a and emp as valid JSON objects (e.g., {"eid": 2, "empname": "jimmy"} for column a).

Step 2: Query the View Correctly #

Now you can use a simple JSON path to filter for empname = 'jimmy':

SELECT * FROM vw_json 
WHERE json_extract(a, '$.empname') = 'jimmy';

Or, use MySQL's shorthand ->> operator for cleaner syntax (available in 5.7+):

SELECT * FROM vw_json 
WHERE a->>'$.empname' = 'jimmy';

This will return exactly the row you expect:

a: {"eid": 2, "empname": "jimmy"}, emp: {"esal": 3000, "edeptno": 3}

Solution 2: Querying a JSON Array View (If You Must Keep Arrays) #

If you need to retain the flat array structure for some reason, first fix the view to avoid concat() (so it stores actual JSON arrays, not strings):

Step 1: Correct the Array-Based View #

CREATE VIEW vw_json_array AS
SELECT 
  json_array('eid', id, 'empname', ename) AS a,
  json_array('esal', sal, 'edeptno', deptno) AS emp
FROM employe;

Now a is a proper JSON array like ["eid", 2, "empname", "jimmy"].

Step 2: Query the Flat Array #

To find the value associated with empname, you need to locate the key's index and then grab the next element (since keys and values alternate):

SELECT * FROM vw_json_array
WHERE json_extract(a, concat('$[', json_unquote(json_search(a, 'one', 'empname')) + 1, ']')) = 'jimmy';

Let's break this down:

• json_search(a, 'one', 'empname') finds the index of the "empname" string (returns "2" in our example).
• json_unquote() removes quotes from the index to convert it to a number.
• Adding 1 gives us the index of the corresponding value (3), which we use in json_extract() to get "jimmy".

Key takeaway: Using JSON objects is almost always better for key-value data because they're easier to query, maintain, and align with standard JSON practices. Stick with json_object() unless you have a specific reason to use flat arrays.

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