Wildcards for JSON Arrays [Deprecated]

FB DocStore is deprecated, and users will need to find alternative solutions.

A common use case for JSON arrays is the ability to match a value to any of the elements in the array. The syntax will leverage the existing document path syntax for arrays. For example, the following query only checks a specific array index in a document:

--
-- {"name":"Alex",
--  "phone":6507770001,
--  "address":{"houseNumber":1001,
--             "street name":"main",
--             "zipcode":98761,
--             "state":"CA"},
--  "cars":["Ford", "Honda", "BMW"]}
--
SELECT doc.name FROM t1 WHERE doc.cars.1 = "BMW";

To find everyone who owns a BMW, we introduce a new wildcard syntax for document arrays.

Syntax

The _ symbol will be used to indicate wildcards to retain the convention of using _ as wildcards for LIKE. Following the earlier BNF notation:

<dot_separated_json_key_path> ::= <json_key_name_or_array_index>
                  | <json_key_name_or_array_index> '.' <dot_separated_json_key_path>
                  | '_'
                  | '_' '.' <dot_separated_json_key_path>

Examples

• In the earlier example, the query to find everyone who owns a BMW is as follows:

SELECT doc.name FROM t1 WHERE doc.cars._ = "BMW";

• Both operands are allowed to be doc paths with wildcards so you can perform comparisons.

SELECT t1.doc.name, t2.doc.name
FROM t1, t2 
WHERE t1.doc.cars._ = t2.doc.cars._;

• Nested array wildcards are also allowed on both operands of the comparison operator (warning: complexity increases).

SELECT t1.doc.name, t2.doc.name
FROM t1, t2
WHERE t1.doc.parents._.children._ LIKE t2.doc.animals._;

• For LIKE, we can also compare with wildcard strings:

SELECT doc.name 
FROM t1 
WHERE doc.cars._ LIKE 'H%';