CockroachDB v2.0-beta如何查询JSONB嵌套属性子集？

当然可以查询JSON blob的嵌套属性啦！CockroachDB v2.0-beta提供了多种JSON操作符，完全支持处理嵌套的JSON结构，我结合你的表结构给你几个实用的场景示例：

1. 用@>操作符匹配嵌套的JSON子集 #

你已经在使用@>来匹配顶层的properties属性，这个操作符其实支持递归匹配更深的嵌套结构。比如如果你的acct列中有类似这样的JSON：

{
  "id": "user123",
  "properties": {
    "foo": "bar",
    "details": {
      "age": 30,
      "city": "New York"
    }
  }
}

想要筛选出properties.details.city等于New York的记录，直接把嵌套结构写进@>的匹配条件里就行：

SELECT acct->>'id' 
FROM account 
WHERE acct @> '{"properties": {"details": {"city": "New York"}}}'

这个查询会自动利用你创建的account_acct_idx倒排索引，效率很高。

2. 用->/->>直接访问嵌套属性 #

如果需要直接针对某个嵌套属性做筛选，或者提取嵌套属性的值，可以用这两个操作符：

• ->：返回JSON类型的属性值
• ->>：返回文本类型的属性值

比如筛选properties.details.age大于25的记录：

SELECT acct->>'id' 
FROM account 
WHERE (acct->'properties'->'details'->>'age')::INT > 25

再比如直接提取嵌套的city属性：

SELECT 
  acct->>'id' AS user_id,
  acct->'properties'->'details'->>'city' AS user_city
FROM account

3. 关于索引的小提示 #

你已经给acct列建了倒排索引，使用@>操作符时会自动用上这个索引。但如果是用->/->>直接筛选嵌套属性，默认可能不会触发倒排索引。如果这类查询很频繁，可以考虑给嵌套属性建一个表达式索引，比如：

CREATE INDEX idx_acct_details_city ON account ((acct->'properties'->'details'->>'city'));

这样针对city的筛选查询就能利用这个索引提升速度了。

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