PostgreSQL查询JSON数组报错：无效JSON输入语法解决问询

Hey there! Let's break down what's happening here and work through solutions. That "invalid input syntax for type json" error almost always means some values in your foo.values column aren't valid JSON—when you hardcode "BR", you're using a valid JSON string, so no error, but converting foo.values hits malformed data.

Step 1: Identify the Bad Data #

First, let's pinpoint which rows are causing the issue. Run this query to spot entries that can't be cast to jsonb:

SELECT values
FROM foo
WHERE values IS NOT NULL
  AND jsonb_valid(values) = false;

This will return all rows where values has invalid JSON syntax (like unclosed quotes, missing commas, or unescaped special characters).

Step 2: Fix the Error #

You have two main paths here:

Option 1: Clean the Invalid Data #

Fix the malformed JSON entries directly. For example:

• If a value is BR instead of "BR", update it to add required quotes:

  UPDATE foo
  SET values = '"' || values || '"'
  WHERE jsonb_valid(values) = false;
  

• Adjust for other syntax issues (like trailing commas or unescaped backslashes) based on what you find in the bad data.

Option 2: Safe Conversion (Avoid Immediate Data Fix) #

If you can't clean the data right now, use PostgreSQL's try_cast (available in PostgreSQL 12+) to safely attempt conversion—invalid values will return NULL instead of throwing an error:

SELECT *
FROM foo
JOIN bar ON bar.value = ANY(SELECT jsonb_array_elements_text(try_cast(foo.values AS jsonb)))
WHERE try_cast(foo.values AS jsonb) IS NOT NULL;

This filters out rows with invalid JSON so your query runs without errors.

Step 3: Optimize Your Query #

For better performance and long-term reliability, here are some best practices:

1. Switch to jsonb Column Type
   If you're storing JSON data permanently, alter the foo.values column to jsonb instead of text. This eliminates runtime casting and ensures only valid JSON is stored:

   ALTER TABLE foo ALTER COLUMN values TYPE jsonb USING try_cast(values AS jsonb);
   

   (Note: This will set invalid entries to NULL—you might want to clean them first.)

2. Add a GIN Index
   If you're frequently checking if bar.value exists in foo.values, add a GIN index to speed up containment checks:

   CREATE INDEX idx_foo_values_gin ON foo USING gin (values);
   

3. Use Native JSONB Operators
   Instead of unnesting arrays, use the @> (contains) operator for cleaner, faster queries. If foo.values is a JSON array of strings, this works perfectly:

   SELECT *
   FROM foo
   JOIN bar ON foo.values @> to_jsonb(bar.value);
   

   This checks if the JSON array in foo.values includes bar.value as an element.

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