In DuckDB, we can use the json_contains() function to check whether a specified JSON value is contained within another JSON structure. It returns a boolean (TRUE or FALSE) that indicates whether or not the value was found. We can also check for key value pairs, or even a full JSON object within the JSON document.

Syntax

json_contains(json_haystack, json_needle)

Parameters:

• json_haystack: The main JSON document (as a string) in which to search.
• json_needle: The value to look for within the json_haystack. It must be:
  • A valid JSON value (object, array, string, number, boolean, or null).
  • If it’s a string, it must be enclosed in double quotes in addition to the single quotes (e.g., '"example"').
  • Numbers don’t need double quotes. (e.g., we can pass '42').
  • Booleans don’t need single quotes (e.g., we can pass true).

Return Type

The function returns a BOOLEAN value:

• TRUE if the json_needle exists anywhere within the json_haystack.
• FALSE otherwise.

Usage Examples

Here are some examples that demonstrate how it works.

Example 1: Value in Array

This example checks to see if a specified number is in the JSON array.

SELECT json_contains('[1, 2, 3]', '2');

Result:

true

And here’s one where the number doesn’t exist:

SELECT json_contains('[1, 2, 3]', '7');

Result:

false

Example 2: Object Contains Value

We can check to see if a value exists within an object:

SELECT json_contains('{"x": 10, "y": 20}', '20');

Result:

true

In this case the value exists in the JSON object.

Here’s what happens if we change the value:

SELECT json_contains('{"x": 10, "y": 20}', '30');

Result:

false

Example 3: Object Contains Key-Value Pair

We can also check to see if an object contains another key-value pair object.

SELECT json_contains('{"name": "Brady", "age": 30}', '{"age": 30}');

Result:

true

And here’s one that returns false:

SELECT json_contains('{"name": "Brady", "age": 30}', '{"age": 70}');

Result:

false

While we’re at it, here’s what happens if we pass the full JSON document, but with the key/value pairs in a different order:

SELECT 
    json_contains('{"name": "Brady", "age": 30}', '{"name": "Brady", "age": 30}') AS original_order,
    json_contains('{"name": "Brady", "age": 30}', '{"age": 30, "name": "Brady"}') AS reversed_order;

Result:

+----------------+----------------+
| original_order | reversed_order |
+----------------+----------------+
| true           | true           |
+----------------+----------------+

Example 4: String Containment (with Double Quotes)

Strings must be wrapped in double quotes to be valid JSON values:

SELECT json_contains('["apple", "banana", "cherry"]', '"banana"');

Result:

true

If we omit the double quotes:

SELECT json_contains('["apple", "banana"]', 'banana');

Result:

Invalid Input Error:
Malformed JSON at byte 0 of input: unexpected character.  Input: banana

Example 5: Nested JSON

We can also check for a nested object inside a larger object:

SELECT json_contains(
  '{"user": {"id": 1, "roles": ["admin", "editor"]}}',
  '{"user": {"id": 1}}'
);

Result:

true

Example 6: Boolean Values

Boolean values don’t need single quotes:

SELECT json_contains('{"name": "Brady", "notifications": true}', true);

Result:

true

But it also works with single quotes:

SELECT json_contains('{"name": "Brady", "notifications": true}', 'true');

Result:

true