Question

我正在寻找在邮政总局9.2号新初专干事职能的一些实例和/或实例。

具体来说,鉴于一系列初专干事的记录:

[
  {name: "Toby", occupation: "Software Engineer"},
  {name: "Zaphod", occupation: "Galactic President"}
]

我将如何写这部书,以找到一份记录?

In vanilla SQL:

SELECT * from json_data WHERE "name" = "Toby"

正式手提手册很稀少:

Update I

I ve put together a gist detailing what is currently possible with PostgreSQL 9.2. Using some custom functions, it is possible to do things like:

SELECT id, json_string(data, name ) FROM things
WHERE json_string(data, name ) LIKE  G% ;

Update II

我现在把我的女神职人员的职能转移到他们自己的项目中:

>PostSQL——将PogreSQL和PL/v8变成一份完全繁多的JSON文件储存的一套功能

Answer 1

Postgres 9.2

页: 1 Andrew Dunstan on the pgsql-hackers list:

At some stage there will possibly be some json-processing (as opposed to json-producing) functions, but not in 9.2.

难道不能阻止他在PLV8中提供应该解决你的问题的榜样。 (链接现已死亡,见现代网站:。)

Postgres 9.3

• 提供新功能和操作人员,增加“json-process”。

回答原始问题,载于第9.3号:

表格:

CREATE TABLE json_tbl (data json);

学历:

SELECT object
FROM   json_tbl
     , json_array_elements(data) AS object
WHERE  object->> name  =  Toby ;

先进实例:

Query combinations with nested array of records in JSON datatype

在较大的表格中,您不妨增加一个表述指数,以提高业绩:

Index for finding an element in a JSON array

Postgres 9.4

添加<><>条码>jsonb (b) 用于“组合”,价值作为本地邮政类型储存,而则属于“。除上述表述索引外,jsonb 还支持GIN, btree and hashindexes, GIN是其中最强大的力量。

The manual on json and jsonb data types and functions.
The Postgres Wiki on JSONB in pg 9.4

The manual goes as far as suggesting:

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

Bold emphasis mine.
Also, performance benefits from general improvements to GIN indexes.

Postgres 9.5

页: 1 添加更多的功能,以操纵jsonb已到位并显示。

Major good news in the release notes of Postgres 9.5.

自那时以来,每个主要的邮政版本的功能和业绩都得到了改善。截至现在,它基本完成(截至第16号邮政)。 ......的一个主要显著增加

Postgres 12

......是SQL/JSON病态语言以及操作人员和职能。现在,对某一表格(<编码>jsonb)的答复可以:

CREATE TABLE jsonb_tbl (data jsonb);

SELECT jsonb_path_query_first(data,  $[*] ? (@.name == "Toby") ) AS object
FROM   jsonb_tbl
WHERE  data @>  [{"name": "Toby"}] ;  -- optional, for index support

或等同:

...
WHERE  data @@  $[*].name == "Toby" ;

https://dbfiddle.uk/pRK2QHpQ”rel=“nofollow noreferer”>fiddle

See:

Returning JSON arrary with particular property using Postgres

大约索引:

Find rows containing a key in a JSONB array of records

Answer 2

With Postgres 9.3+, just use the -> operator. For example,

SlectT data->text ->thumbnail ->url AS thumb from instagram;

http://clarkdave.net/06/what-can-you-do-with-postgresql-and-json/“rel=“noreferer” http://clarkdave.net/06/what-can-you-do-with-postgresql-and-json/。某些冰箱的例子和辅导。

Answer 3

邮政9.3 用途->用途使用。 4 例

种子。

se = SmartElement.new
se.data = 
{
    params:
    [
        {
            type: 1,
            code: 1,
            value: 2012,
            description:  year of producction 
        },
        {
            type: 1,
            code: 2,
            value: 30,
            description:  length 
        }
    ]
}

se.save

铁路c

SELECT data-> params ->0 as data FROM smart_elements;

回返s

                                 data
----------------------------------------------------------------------
 {"type":1,"code":1,"value":2012,"description":"year of producction"}
(1 row)

您可以继续nes

SELECT data-> params ->0-> type  as data FROM smart_elements;

回返

 data
------
 1
(1 row)