JSON Overview

JSON Overview

DuckDB supports SQL functions that are useful for reading values from existing JSON and creating new JSON data. JSON is supported with the json extension which is shipped with most DuckDB distributions and is auto-loaded on first use. If you would like to install or load it manually, please consult the “Installing and Loading” page.

About JSON

JSON is an open standard file format and data interchange format that uses human-readable text to store and transmit data objects consisting of attribute–value pairs and arrays (or other serializable values). While it is not a very efficient format for tabular data, it is very commonly used, especially as a data interchange format.

JSONPath and JSON Pointer Syntax

DuckDB implements multiple interfaces for JSON extraction: JSONPath and JSON Pointer. Both of them work with the arrow operator (->) and the json_extract function call.

Note that DuckDB only supports lookups in JSONPath, i.e., extracting fields with .<key> or array elements with [<index>]. Arrays can be indexed from the back and both approaches support the wildcard *. DuckDB not support the full JSONPath syntax because SQL is readily available for any further transformations.

It's best to pick either the JSONPath or the JSON Pointer syntax and use it in your entire application.

Indexing

Warning

Following PostgreSQL's conventions, DuckDB uses 1-based indexing for its ARRAY and LIST data types but 0-based indexing for the JSON data type.

Examples

Loading JSON

Read a JSON file from disk, auto-infer options:

SELECT * FROM 'todos.json';

Use the read_json function with custom options:

SELECT * FROM read_json('todos.json', format = 'array', columns = {userId: 'UBIGINT', id: 'UBIGINT', title: 'VARCHAR', completed: 'BOOLEAN'});

Read a JSON file from stdin, auto-infer options:

cat data/json/todos.json | duckdb -c "SELECT * FROM read_json('/dev/stdin')"

Read a JSON file into a table:

CREATE TABLE todos (userId UBIGINT, id UBIGINT, title VARCHAR, completed BOOLEAN); COPY todos FROM 'todos.json' (AUTO_DETECT true);

Alternatively, create a table without specifying the schema manually with a CREATE TABLE ... AS SELECT clause:

CREATE TABLE todos AS SELECT * FROM 'todos.json';

Since DuckDB v1.3.0, the JSON reader returns the filename virtual column:

SELECT filename, * FROM 'todos-*.json';

Writing JSON

Write the result of a query to a JSON file:

COPY (SELECT * FROM todos) TO 'todos.json';

JSON Data Type

Create a table with a column for storing JSON data and insert data into it:

CREATE TABLE example (j JSON); INSERT INTO example VALUES ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

Retrieving JSON Data

Retrieve the family key's value:

SELECT j.family FROM example;

"anatidae"

Extract the family key's value with a JSONPath expression as JSON:

SELECT j->'$.family' FROM example;

"anatidae"

Extract the family key's value with a JSONPath expression as a VARCHAR:

SELECT j->>'$.family' FROM example;

anatidae

Using Quotes for Special Characters

JSON object keys that contain the special [ and . characters can be used by surrounding them with double quotes ("):

SELECT '{"d[u]._\"ck":42}'->'$."d[u]._\"ck"' AS v;

42

网址：JSON Overview https://www.yuejiaxmz.com/news/view/1237957

相关内容

Golang处理JSON（一）
JSON 格式化
C#对Json数据进行序列化
高效便捷：本地JSON格式化小工具推荐
在 iPhone 和 iPad 上的“快捷指令”中使用 JSON 的介绍
Hutool实战：高效工具库的缓存、JSON、加解密与更多
菜鸟教程带你轻松掌握json格式解析与应用技巧全方位教程
json格式用什么打开才能查看和编辑这些数据文件的完整方法和工具推荐
Postman工具访问 Spring Boot项目 通过requestbody用json封装传参 使用@RequestBody注解获取数据
Node读取JSON文件并在for循环中的延时Sleep处理（延时任务清洗数据接口实战演示）

随便看看