如何从表中提取JSON数据值？查询含Stupid的JSON无结果求助

Hey there! Let's break down why your SELECT * FROM UNKNOWN WHERE CONTAINS(Value, 'Stupid') query isn't returning results, and how to fix it.

The core problem is that CONTAINS is built for full-text search on plain text columns—not structured JSON data. Most databases have dedicated functions to query JSON fields, so we need to use those instead. Below are solutions for the most common database systems:

1. SQL Server #

SQL Server has robust JSON support. To check if the Stupid key exists in your Value column, use either JSON_VALUE or JSON_QUERY (depending on whether you need the value or just to confirm the key exists):

-- Check if the 'Stupid' key has a non-null value
SELECT * 
FROM UNKNOWN 
WHERE JSON_VALUE(Value, '$.Stupid') IS NOT NULL;

-- Alternatively, just verify the key exists (even if value is null)
SELECT * 
FROM UNKNOWN 
WHERE JSON_QUERY(Value, '$.Stupid') IS NOT NULL;

Also, double-check that your JSON is properly formatted! Your sample data uses unquoted Yes/No values, which aren't valid JSON. Make sure boolean values are either quoted strings ("Yes") or proper JSON booleans (true/false), otherwise SQL Server won't parse the column as valid JSON. You can filter valid rows first with:

SELECT * 
FROM UNKNOWN 
WHERE ISJSON(Value) = 1;

2. MySQL #

In MySQL, use JSON_CONTAINS_PATH to check for the presence of a specific key:

-- 'one' means we're checking for at least one matching key
SELECT * 
FROM UNKNOWN 
WHERE JSON_CONTAINS_PATH(Value, 'one', '$.Stupid') = 1;

Or use JSON_EXTRACT to verify the key has a value:

SELECT * 
FROM UNKNOWN 
WHERE JSON_EXTRACT(Value, '$.Stupid') IS NOT NULL;

3. PostgreSQL #

PostgreSQL uses operators like ? for JSON/JSONB columns to check if a key exists:

-- For json or jsonb columns
SELECT * 
FROM UNKNOWN 
WHERE Value ? 'Stupid';

If you're working with older PostgreSQL versions that don't support the ? operator, you can use json_object_keys:

SELECT *
FROM UNKNOWN
WHERE 'Stupid' IN (SELECT json_object_keys(Value));

Quick Notes #

• Always ensure your JSON data is valid—unquoted values, missing commas, or mismatched brackets will break JSON-specific functions.
• If you plan to query JSON fields frequently, consider using a dedicated JSON type (like jsonb in PostgreSQL or NVARCHAR(MAX) with JSON validation in SQL Server) for better performance and functionality.

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