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://mxgxt.com/news/view/1681175

相关内容

json在线解析及格式化
Learn Scala
打渔日历API接口全解析：每日更新摸鱼日报、明星段子、新闻简报等多元化内容，支持图片与JSON格式输出，助力开发者打造个性化内容展示与数据分析，提升产品竞争力与用户粘性！
明星雇员 台词:影视巨星JSON标准化台词处理
艾米丽·罗斯人物关系,合作过的明星
摸鱼日历，新闻简报等一些每日更新的API接口分享
摸鱼日报
2019集美天宸地产项目产品发布会暨明星助力出道见面会活动策划方案（55页）.pptx
DataWorks创建独享数据集成资源、独享调度资源组的功能有哪些？
FongMi影视、tvbox、猫影视配置文件 – APP喵

随便看看