基于PostgreSQL的json操作
why pgsql
- 开源，mssql也可以实现
- 我是从事gis相关开发工作，大部分时间使用postgis
- 存储类型多，包括数组、json等。可以自定义类型
pgsql 中的json与jsonb
- 这里不过多证明两者性能差异，结论是(这里指性能)，写入：json > jsonb 读取：json < jsonb 。
- 推荐使用jsonb，在npgsql中C#类型JsonDocument也是直接被映射为jsonb。
官方文档
- npgsql
- postgresql
sql json 基本操作
说明 : copy 的官方实例
- json & jsonb 操作符
| 操作符 | 右操作符类型 | 返回类型 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|---|
| -> | int | json or jsonb | 获取数组元素(0 开始，负数从末尾开始计) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
| -> | text | json or jsonb | 获取对象域 | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
| #> | text[] | json or jsonb | 获取在指定路径的 JSON 对象 | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
方便记忆 ：如果操作符多加一个 > 如：->> 、 #>> 表示返回类型为text
- jsonb 额外的操作符
| 操作符 | 右操作符类型 | 描述 | 例子 |
|---|---|---|---|
| @> | jsonb | 左边的 JSON 值是否在顶层包含右边的 JSON 路径/值项？ | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
| <@ | jsonb | 左边的 JSON 路径/值项是否被包含在右边的 JSON 值的顶层？ | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
| ? | text | 键/元素字符串是否存在于 JSON 值的顶层？ | '{"a":1, "b":2}'::jsonb ? 'b' |
| ?| | text[] | 数组中字符串 any 存在与顶层 | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
| ?& | text[] | 数组中字符串 all 存在与顶层 | '["a", "b"]'::jsonb ?& array['a', 'b'] |
| || | jsonb | 把两个jsonb值串接成一个新的jsonb值 | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
| - | text | 删除对应键 | '{"a": "b"}'::jsonb - 'a' |
| - | text[] | 删除所有匹配的键 | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
| - | integer | 删除对应索引数组元素 ，顶层容器不是数组则抛出错误 | '["a", "b"]'::jsonb - 1 |
| #- | text[] | 删除指定路径的域或者元素 | ["a", {"b":1}]'::jsonb #- '{1,b}' |
| @? | jsonpath | JSON路径是否返回指定的JSON值的任何项目？ | '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' |
| @@ | jsonpath | 返回指定的JSON路径谓词检查结果。只考虑结果的第一项。 如果结果不是布尔值，那么返回 null 。 | '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' |
- json 的pgsql函数就不copy了
efcore中使用
-
entity定义
- 使用固定的结构
public class Customer { public int Id { get; set; } [Column(TypeName = "jsonb")] public Order[] Orders { get; set; } } public class Order { public string Name { get; set; } public decimal Price { get; set; } }- 使用动态结构
public class Customer:IDisposable { public int Id { get; set; } public JsonDocument Orders { get; set; } public void Dispose() => Orders?.Dispose(); } -
查询
npgsql对jsonb查询的功能很弱，所以查询整个实体我推荐直接使用DbSet进行查询，如果需要对返回数据重新塑性则使用Microsoft.EntityFrameworkCore.Relational 程序集对sql查询进行扩展。().FromSqlInterpolated()