PostgreSQL（五）—— jsonpath

低调的牛魔王
697 阅读2分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第22天，点击查看活动详情

引言

上一篇实战 jsonb 和 json 类型的区别，以及存储和查询 PostgreSQL JSON 类型数据的一些函数和操作方法。本篇介绍 PostgreSQL 另一种高效查询的 JSON 类型数据 jsonpath 的使用。

jsonpath Type 定义及规范

jsonpath 类型实现了对 PostgreSQL 中 SQL/JSON 路径语言（path language）的支持，以有效地查询 JSON 数据。jsonpath 提供已分析的 SQL/JSON 路径表达式的二进制表示形式，该表达式指定路径引擎要从 JSON 数据中检索的项目，以便使用 SQL/JSON 查询函数进行进一步处理。

jsonpath 变量访问规范

SQL/JSON 路径解析的函数动词和运算符的语义通常遵循 SQL 标准。同时，为了提供一种自然的方式来处理 JSON 数据，SQL/JSON 路径语法使用了一些 JavaScript 约定：

  • 点 （.） 用于成员访问
  • 方括号 （[]） 用于数组访问
  • SQL/JSON 数组索引是 0 开始的，不像常规 SQL 数组从 1 开始

jsonpath 字符串编写的规范

SQL/JSON 路径表达式通常在 SQL 查询中作为 SQL 字符串文本编写，因此字符串文本必须括在单引号''中，并且字符串值中所需的任何单引号都必须使用双引号"。某些形式的路径表达式需要其中的一些特殊的字符串文本，这些嵌入的字符串文字遵循 JavaScript/ECMAScript 约定：它们必须用双引号括起来，并且可以在其中使用反斜杠转义来表示难以键入的字符。特别是，在嵌入字符串文本中编写双引号的方法是"，而要编写反斜杠本身，则必须编写\。

jsonpath 元素

路径表达式由一系列路径元素组成，这些元素为：

  • JSON 基元类型的：Unicode 编码的内容、数字、布尔值（true or false）或 null。

  • jsonpath Variables

    VariableDescription
    $变量，表示要查询的 JSON 值
    $varname指定变量，可以在 JSON 函数处理中设置具体的名称
    @表示筛选器表达式中路径计算结果的变量

  • jsonpath Accessors

    Accessor OperatorDescription
    .key
    ."$varname"    		成员访问器，返回具有指定键的对象成员。如果对象的 key 中含有特殊字符，则需要放到双引号中进行访问
    .*通配符成员访问器，返回位于当前对象顶层的所有成员的值。
    .**递归通配符成员访问器，用于处理当前对象的 JSON 层次结构的所有级别，并返回所有成员值，而不考虑其嵌套级别。这是 SQL/JSON 标准的 PostgreSQL 扩展
    .{level}
    .    {start_level to end_level}    		与 .** 类似，但只返回指定的 JSON 层次结构
    [subscript, ...]数组元素访问器，下标可以以两种形式给出：索引或s tart_index 到 end_index
    [*]数组通配符访问器，返回所有数组元素

  • jsonpath 运算符和方法 Section 9.16.2.2

  • 括号，可用于提供筛选器表达式或定义路径计算的顺序。

jsonpath 实战

创建 schema

CREATE TABLE IF NOT EXISTS public.jsonpath_demo
(
    id bigint NOT NULL DEFAULT nextval('jsonpath_demo_id_seq'::regclass),
    json jsonb,
    CONSTRAINT jsonpath_demo_pkey PRIMARY KEY (id)
)

存储一个复杂地理信息的六边形 json 对象

将一个 GIS 空间六边形按 postgresql jsonb 类型存储

image-20221215133100040.png

查询具体某个变量的值

select jsonb_path_query(json::jsonb,'$.features.geometry') as gis_geometry from jsonpath_demo;

查询结果

{
  "type": "LineString",
  "coordinates": [
    [
      51.182534788151585,
      25.31312193518845
    ],
    [
      51.18202651643904,
      25.311382766878683
    ],
    [
      51.18327667426206,
      25.310142639993114
    ],
    [
      51.185035062550796,
      25.310641652273205
    ],
    [
      51.18554336101001,
      25.312380770338834
    ],
    [
      51.184293244435636,
      25.313620926368095
    ],
    [
      51.182534788151585,
      25.31312193518845
    ]
  ]
}

查询数组

select jsonb_path_query_array(json::jsonb,'$.features.geometry.coordinates') as gis_coordinates from jsonpath_demo;

查询结果

[  [    [      51.182534788151585,      25.31312193518845    ],
    [      51.18202651643904,      25.311382766878683    ],
    [      51.18327667426206,      25.310142639993114    ],
    [      51.185035062550796,      25.310641652273205    ],
    [      51.18554336101001,      25.312380770338834    ],
    [      51.184293244435636,      25.313620926368095    ],
    [      51.182534788151585,      25.31312193518845    ]
  ]
]
avatar
文章
阅读
粉丝