SQL 与 JSON 互转

Goose 提供了在 SQL 与 JSON 之间对 SELECT 语句进行序列化/反序列化的函数，也支持执行 JSON 序列化后的语句。

函数	类型	说明
`json_deserialize_sql(json)`	标量函数	将一个或多个 JSON 序列化语句反序列化为等价 SQL 字符串。
`json_execute_serialized_sql(varchar)`	表函数	执行 JSON 序列化语句并返回结果行。当前仅支持一次执行一条语句。
`json_serialize_sql(varchar, skip_default := boolean, skip_empty := boolean, skip_null := boolean, format := boolean)`	标量函数	将以分号（`;`）分隔的一组 SELECT 语句序列化为等价的 JSON 语句列表。
`PRAGMA json_execute_serialized_sql(varchar)`	Pragma	`json_execute_serialized_sql` 的 Pragma 版本。

json_serialize_sql(varchar) 支持可选参数 skip_empty、skip_null、format，可用于控制序列化输出。

如果在事务内部执行 json_execute_serialized_sql(varchar) 表函数，被执行的序列化语句将看不到事务内本地变更。原因是这些语句在独立查询上下文中执行。你可以使用 PRAGMA json_execute_serialized_sql(varchar) 版本，在与 pragma 相同的查询上下文中执行；但限制是序列化 JSON 必须是常量字符串，也就是说不能写成 PRAGMA json_execute_serialized_sql(json_serialize_sql(...))。

注意，这些函数不会保留类似 FROM * SELECT ... 这样的语法糖。因此，经过 json_deserialize_sql(json_serialize_sql(...)) 往返后的语句可能与原语句文本不完全一致，但语义应等价且输出一致。

示例

简单示例：

SELECT json_serialize_sql('SELECT 2');

{"error":false,"statements":[{"node":{"type":"SELECT_NODE","modifiers":[],"cte_map":{"map":[]},"select_list":[{"class":"CONSTANT","type":"VALUE_CONSTANT","alias":"","query_location":7,"value":{"type":{"id":"INTEGER","type_info":null},"is_null":false,"value":2}}],"from_table":{"type":"EMPTY","alias":"","sample":null,"query_location":18446744073709551615},"where_clause":null,"group_expressions":[],"group_sets":[],"aggregate_handling":"STANDARD_HANDLING","having":null,"sample":null,"qualify":null},"named_param_map":[]}]}

多语句与 skip 选项示例：

SELECT json_serialize_sql('SELECT 1 + 2; SELECT a + b FROM tbl1', skip_empty := true, skip_null := true);

{"error":false,"statements":[{"node":{"type":"SELECT_NODE","select_list":[{"class":"FUNCTION","type":"FUNCTION","query_location":9,"function_name":"+","children":[{"class":"CONSTANT","type":"VALUE_CONSTANT","query_location":7,"value":{"type":{"id":"INTEGER"},"is_null":false,"value":1}},{"class":"CONSTANT","type":"VALUE_CONSTANT","query_location":11,"value":{"type":{"id":"INTEGER"},"is_null":false,"value":2}}],"order_bys":{"type":"ORDER_MODIFIER"},"distinct":false,"is_operator":true,"export_state":false}],"from_table":{"type":"EMPTY","query_location":18446744073709551615},"aggregate_handling":"STANDARD_HANDLING"}},{"node":{"type":"SELECT_NODE","select_list":[{"class":"FUNCTION","type":"FUNCTION","query_location":23,"function_name":"+","children":[{"class":"COLUMN_REF","type":"COLUMN_REF","query_location":21,"column_names":["a"]},{"class":"COLUMN_REF","type":"COLUMN_REF","query_location":25,"column_names":["b"]}],"order_bys":{"type":"ORDER_MODIFIER"},"distinct":false,"is_operator":true,"export_state":false}],"from_table":{"type":"BASE_TABLE","query_location":32,"table_name":"tbl1"},"aggregate_handling":"STANDARD_HANDLING"}}]}

跳过 AST 中默认值（如 "distinct":false）：

SELECT json_serialize_sql('SELECT 1 + 2; SELECT a + b FROM tbl1', skip_default := true, skip_empty := true, skip_null := true);

{"error":false,"statements":[{"node":{"type":"SELECT_NODE","select_list":[{"class":"FUNCTION","type":"FUNCTION","query_location":9,"function_name":"+","children":[{"class":"CONSTANT","type":"VALUE_CONSTANT","query_location":7,"value":{"type":{"id":"INTEGER"},"is_null":false,"value":1}},{"class":"CONSTANT","type":"VALUE_CONSTANT","query_location":11,"value":{"type":{"id":"INTEGER"},"is_null":false,"value":2}}],"order_bys":{"type":"ORDER_MODIFIER"},"is_operator":true}],"from_table":{"type":"EMPTY"},"aggregate_handling":"STANDARD_HANDLING"}},{"node":{"type":"SELECT_NODE","select_list":[{"class":"FUNCTION","type":"FUNCTION","query_location":23,"function_name":"+","children":[{"class":"COLUMN_REF","type":"COLUMN_REF","query_location":21,"column_names":["a"]},{"class":"COLUMN_REF","type":"COLUMN_REF","query_location":25,"column_names":["b"]}],"order_bys":{"type":"ORDER_MODIFIER"},"is_operator":true}],"from_table":{"type":"BASE_TABLE","query_location":32,"table_name":"tbl1"},"aggregate_handling":"STANDARD_HANDLING"}}]}

语法错误示例：

SELECT json_serialize_sql('TOTALLY NOT VALID SQL');

{"error":true,"error_type":"parser","error_message":"syntax error at or near \"TOTALLY\"","error_subtype":"SYNTAX_ERROR","position":"0"}

反序列化示例：

SELECT json_deserialize_sql(json_serialize_sql('SELECT 1 + 2'));

SELECT (1 + 2)

带语法糖的反序列化示例（转换过程中语法糖会丢失）：

SELECT json_deserialize_sql(json_serialize_sql('FROM x SELECT 1 + 2'));

SELECT (1 + 2) FROM x

执行示例：

SELECT * FROM json_execute_serialized_sql(json_serialize_sql('SELECT 1 + 2'));

报错示例：

SELECT * FROM json_execute_serialized_sql(json_serialize_sql('TOTALLY NOT VALID SQL'));

Parser Error:
Error parsing json: parser: syntax error at or near "TOTALLY"

示例​

示例