17575人阅读
数据库（58）
web开发（141）
&shows the operators that are available for
use with JSON (see&) data.
Table 9-39. JSON Operators
Right Operand Type
Description
Get JSON array element
'[1,2,3]'::json-&2
Get JSON object field
'{&a&:1,&b&:2}'::json-&'b'
Get JSON array element as text
'[1,2,3]'::json-&&2
Get JSON object field as text
'{&a&:1,&b&:2}'::json-&&'b'
array of text
Get JSON object at specified path
'{&a&:[1,2,3],&b&:[4,5,6]}'::json#&'{a,2}'
array of text
Get JSON object at specified path as text
'{&a&:[1,2,3],&b&:[4,5,6]}'::json#&&'{a,2}'
&shows the functions that are available for
creating and manipulating JSON (see&) data.
Table 9-40. JSON Support Functions
Return Type
Description
Example Result
array_to_json(anyarray [, pretty_bool])
Returns the array as JSON. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension 1 elements if&pretty_bool&is true.
array_to_json('{{1,5},{99,100}}'::int[])
[[1,5],[99,100]]
row_to_json(record [, pretty_bool])
Returns the row as JSON. Line feeds will be added between level 1 elements if&pretty_bool&is true.
row_to_json(row(1,'foo'))
{&f1&:1,&f2&:&foo&}
to_json(anyelement)
Returns the value as JSON. If the data type is not built in, and there is a cast from the type to&json, the cast function will be used to perform the conversion. Otherwise, for any value other than a number, a Boolean, or a null value,
the text representation will be used, escaped and quoted so that it is legal JSON.
to_json('Fred said &Hi.&'::text)
&Fred said \&Hi.\&&
json_array_length(json)
Returns the number of elements in the outermost JSON array.
json_array_length('[1,2,3,{&f1&:1,&f2&:[5,6]},4]')
json_each(json)
SETOF key text, value json
Expands the outermost JSON object into a set of key/value pairs.
select * from json_each('{&a&:&foo&, &b&:&bar&}')
key | value
-----+-------
json_each_text(from_json json)
SETOF key text, value text
Expands the outermost JSON object into a set of key/value pairs. The returned value will be of type text.
select * from json_each_text('{&a&:&foo&, &b&:&bar&}')
key | value
-----+-------
json_extract_path(from_json json, VARIADIC path_elems text[])
Returns JSON object pointed to by&path_elems.
json_extract_path('{&f2&:{&f3&:1},&f4&:{&f5&:99,&f6&:&foo&}}','f4')
{&f5&:99,&f6&:&foo&}
json_extract_path_text(from_json json, VARIADIC path_elems text[])
Returns JSON object pointed to by&path_elems.
json_extract_path_text('{&f2&:{&f3&:1},&f4&:{&f5&:99,&f6&:&foo&}}','f4', 'f6')
json_object_keys(json)
SETOF text
Returns set of keys in the JSON object. Only the&&outer&&object will be displayed.
json_object_keys('{&f1&:&abc&,&f2&:{&f3&:&a&, &f4&:&b&}}')
json_object_keys
------------------
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]
anyelement
Expands the object in&from_json&to a row whose columns match the record type defined by base. Conversion columns in base with no corresponding key in&from_json&will
be left null. If a column is specified more than once, the last value is used.
select * from json_populate_record(null::x, '{&a&:1,&b&:2}')
---+---
json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]
SETOF anyelement
Expands the outermost set of objects in&from_json&to a set whose columns match the record type defined by base. Conversion columns in base with no corresponding key in&from_json&will
be left null. If a column is specified more than once, the last value is used.
select * from json_populate_recordset(null::x, '[{&a&:1,&b&:2},{&a&:3,&b&:4}]')
---+---
json_array_elements(json)
SETOF json
Expands a JSON array to a set of JSON elements.
json_array_elements('[1,true, [2,false]]')
-----------
Note:&The&json&functions and operators can impose stricter validity requirements than the type's input functions. In particular, they check
much more closely that any use of Unicode surrogate pairs to designate characters outside the Unicode Basic Multilingual Plane is correct.
Note:&Many of these functions and operators will convert Unicode escapes in the JSON text to the appropriate UTF8 character when the database encoding is UTF8. In
other encodings the escape sequence must be for an ASCII character, and any other code point in a Unicode escape sequence will result in an error. In general, it is best to avoid mixing Unicode escapes in JSON with a non-UTF8 database encoding, if possible.
Note:&The&&extension has a cast from&hstore&to&json,
so that converted&hstore&values are represented as JSON objects, not as string values.
See also&&about the aggregate function&json_agg&which
aggregates record values as JSON efficiently.
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1667447次
积分：17009
积分：17009
排名：第633名
原创：264篇
转载：434篇
评论：234条
(2)(2)(1)(7)(8)(8)(8)(3)(2)(11)(2)(5)(6)(1)(4)(25)(21)(12)(12)(16)(6)(22)(33)(20)(31)(10)(4)(5)(19)(10)(4)(14)(16)(10)(24)(8)(63)(19)(25)(22)(44)(13)(7)(15)(15)(22)(14)(41)(11)
(window.slotbydup = window.slotbydup || []).push({
id: '4740887',
container: s,
size: '250,250',
display: 'inlay-fix'在 SegmentFault，解决技术问题
每个月，我们帮助 1000 万的开发者解决各种各样的技术问题。并助力他们在技术能力、职业生涯、影响力上获得提升。
一线的工程师、著名开源项目的作者们，都在这里：
获取验证码
已有账号？
问题对人有帮助，内容完整，我也想知道答案
问题没有实际价值，缺少关键内容，没有改进余地
数据表中有个字段 options 类型为 json.这个字段中存储的数据如下：
"option_title" : "A选项",
"score" : "1",
"creation_date" : "T17:25:55+08:00"
"option_title" : "B选项",
"score" : "0",
"creation_date" : "T17:25:55+08:00"
"option_title" : "C选项",
"score" : "0",
"creation_date" : "T17:25:55+08:00"
"option_title" : "D选项",
"score" : "0",
"creation_date" : "T17:25:55+08:00"
请问：怎么查询 option_title = "A选项" 的。PostgreSQL 版本： 9.5
答案对人有帮助，有参考价值
答案没帮助，是错误的答案，答非所问
好吧，自己昨天已经找到一个解决办法了。现在贴出来。
/* 先转换 options 这个 json 字段，然后再按一般查 json 的方法查询即可。 */
select * from (
select id, title, json_array_elements(options) as data from question
where data-&&'option_title'::varchar = 'A选项';
同步到新浪微博
分享到微博？
关闭理由：
删除理由：
忽略理由：
推广（招聘、广告、SEO 等）方面的内容
与已有问题重复（请编辑该提问指向已有相同问题）
答非所问，不符合答题要求
宜作评论而非答案
带有人身攻击、辱骂、仇恨等违反条款的内容
无法获得确切结果的问题
非开发直接相关的问题
非技术提问的讨论型问题
其他原因（请补充说明）
我要该，理由是：使用PostgreSQL中的row_to_json()直接获得JSON（PG JSON系列1）
已有 2192 次阅读
|个人分类:|系统分类:|关键词:POSTGRESQL, JSON, JSONB
本系列博文讲述使用PG JSON/JSONB类型考虑的问题，这是第一篇（我尽量写的简单，让初学者跟着做很容易也能重复实验），看看不使用JSON/JSONB，仅仅使用row_to_json()函数能帮我们做什么。 & &JSON数据类型在当前互联网浪潮中得到了大量的采用，见下图GOOGLE指数对比JSON和XML，从全球范围来看：蓝色表示JSON的趋势，成明显的上升趋势。从中国范围来看，JSON搜索热度明显高于XML：PG也从9.2开始正式引入JSON类型，那自然就问：既然DB支持了JSON和JSON相关函数，是不是中间服务层（如PHP,JAVA）通过JDBC可以直接获取到JSON数据，而无需再用org.json和json-lib库把以前的行数据进行转换？我们来做个实验：运行环境：PG 9.6，WIN8.1 64，Tomcat 8.0，JDK 8-- 创建数据库--postgres登陆pg，使用如下sql创建数据库和用户名密码：CREATE ROLE jsontest PASSWORD 'jsontest.dba' CREATEDB &NOSUPERUSER CREATEROLE LOGIN;CREATE DATABASE jsontestdb OWNER jsontest ENCODING 'UTF8';-- 用jsontest登录，然后是DDL：-- 创建一个全局的序列DROP SEQUENCE IF EXISTS jsontest_uuid_seq CASCADE;CREATE SEQUENCE jsontest_uuid_seq START 1; -- Table: TUsers, 用户表DROP TABLE IF EXISTS TUsers CASCADE;CREATE TABLE TUsers ( &id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 用户id &realName character varying(64) -- 真实姓名 & )WITH ( OIDS=FALSE );CREATE INDEX TUsers_cellphone_idx ON TUsers (realName);-- Table: TProject,用户创建的项目表DROP TABLE IF EXISTS TProject CASCADE;CREATE TABLE TProject ( &id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 活动id
&title character varying(256) NOT NULL UNIQUE, -- 项目名称,设置为UNIQUE，避免混淆 &creatorId integer DEFAULT NULL REFERENCES TUsers (id) match simple on delete SET NULL -- 活动创建的用户id &
&)WITH ( OIDS=FALSE );CREATE INDEX TProject_creator_idx ON TProject( creatorId );-- 测试数据：insert into TUsers values(1,'test1');insert into TUsers values(2,'test2');insert into TUsers values(3,'test3');insert into TProject values(1,'测试项目1',1);insert into TProject values(2,'测试项目2',1);insert into TProject values(3,'测试项目3',1);DB设计图（在Eclipse用ERMaster反向生成）如下：好，JAVA这一块，我们分多种情况来分析：1. 数据库最多只会返回一行数据，然后转换成JSON对象：如通过用户id获得用户JSON对象public class User { & &public User() { & & & &super(); & &} /* &* 根据用户id获取用户信息 &*/ public String getUserInfoFromId(String userId){String res =if(null == &userId || userId.isEmpty() )C & &S & &ResultS & &DBPool dbp = new DBPool(); & &conn = dbp.getConnection(); & &try { & & & &stm = conn.createStatement(); & & & &String sql = &SELECT row_to_json(a.*) from TUsers a where a.id = &+userId; & & & &rs = stm.executeQuery(sql); & & & &if(rs != null && rs.next()){ & & & & & &res = rs.getString(1);
& & & &} & &} catch (SQLException e) { & & & &e.printStackTrace(); & &} finally{ & & & &dbp.closeConnection(); & &} & & & & }}上面的核心是SQL：SELECT row_to_json(a.*) from TUsers a where a.id = 1 那如果我获得的不是a.*，即不需要一整行数据（如密码不想返回）怎么办？如下会报错：SELECT row_to_json(a.id,a.realName) from TUsers a where a.id = 1 解决办法有：SELECT row_to_json(a.*) from (select b.id,b.realName from TUsers b where b.id = 1 ) a 或者使用WITH：WITH myInfo AS (select a.id,a.realName from TUsers a where a.id = 1 )SELECT row_to_json(b.*) from myInfo b或者：SELECT
& (SELECT row_to_json(_) from (select a.id, a.realName) as _)from & TUsers a where a.id = 1关于这个问题可以参考： 注意的是，不能用row()函数，因为无法产生别名，即正如https://www.postgresql.org/docs/9.6/static/functions-json.html里所演示的那样：row_to_json(row(1,'foo')){&f1&:1,&f2&:&foo&}所以综合以上，建议用WITH方法，简洁明了。public class UserWS extends HttpServlet ：protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { & &String rtn = &&; & &request.setCharacterEncoding(&UTF-8&); & &response.setContentType(&text/ charset=UTF-8&); & & &response.setCharacterEncoding(&UTF-8&); & &String action = request.getParameter(&action&); & &if(action.equalsIgnoreCase(&getuserinfo&)) {//其他人通过用户ID获取用户信息 & & & &String userid = (String) request.getParameter(&userid&); & & & &User u = new User(); & & & &String userJson = u.getUserInfoFromId(userid); & & & &if(null != userJson){//存在该用户信息 & & & & & &rtn = new StringBuilder() & & & & & & & &.append( &{&issuccessful&:&true&,&data&:&) & & & & & & & &.append((null==userJson)?&&:userJson) & & & & & & & &.append(&}&) & & & & & & & &.toString(); & & & &}else{ & & & & & &rtn = &{&issuccessful&:&false&,&errorcode&:&NOEXIST_USER&}&; & & & & & }} & &String callbackFunName = request.getParameter(&callbackparam&); & &if(null != callbackFunName ){ & & & &response.getOutputStream().write(StringUtil.wrapCrossDomain(rtn,callbackFunName).getBytes(&UTF-8&));
& &}else{ & & & &response.getOutputStream().write(rtn.getBytes(&UTF-8&));
& &}}测试一下，当我们发送： 返回：{
& &issuccessful&:&true&, & &data&:{
& &id&:1, &
& &realname&:&test1& & }}2. 数据库返回多行数据：如获取用户参与的项目WITH myProjects AS (select a.id,a.title from TProject a where a.creatorId = 1) SELECT row_to_json(b.*) from (SELECT array_to_json(array(select row_to_json(myProjects.*) &from myProjects),false) as myProjects) b测试一下： 返回：{ & &issuccessful&:&true&, & &data&:{ & & &&myprojects&:[ & & & & { & & & & & &&id&:3, & & & & & &&title&:&测试项目3& & & & & }, & & & & { & & & & & &&id&:2, & & & & & &&title&:&测试项目2& & & & & }, & & & & { & & & & & &&id&:1, & & & & & &&title&:&测试项目1& & & & & } & & &] & }}3. 返回某些表的一行数据，加上某些表的多行数据：如获取用户基本信息以及用户参与的项目WITH &myInfo AS (select id,realName from TUsers where id = 1 ), -- 一行数据 &myProjects AS (select a.id,a.title from TProject a,myInfo b where a.creatorId = b.id) -- 多行数据SELECT row_to_json(x.*) from (select &c.*,d.* &from myInfo c,(SELECT array_to_json(array(select row_to_json(myProjects.*) &from myProjects),false) as myProjects) d -- 把多行数据生成一行一列json数组) x该SQL返回：{
& &myprojects&:[
& &id&:3, &
& &title&:&测试项目3& &
& &id&:2, &
& &title&:&测试项目2& &
& &id&:1, &
& &title&:&测试项目1& &
& } & ], & &id&:1, & &realname&:&test1&}总结一下：仅仅使用PG的row_to_json()，即可轻松返回需要的各种SQL数据。这样子使得中间层（JAVA/PHP）的代码变得简化，无需org.json和json-lib库再做一次封装，这是架构师要注意的一个重要趋势。加我私人微信，交流技术。
转载本文请联系原作者获取授权，同时请注明本文来自孙鹏科学网博客。链接地址：
上一篇：下一篇：
当前推荐数：0
评论 ( 个评论)
扫一扫，分享此博文
作者的其他最新博文
热门博文导读
Powered by
Copyright &比特客户端
您的位置：
详解大数据
详解大数据
详解大数据
详解大数据
正确使用PostgreSQL的数组类型
关键字：Web
　　在Heap中，我们依靠PostgreSQL支撑大多数后端繁重的任务，我们每个事件为一个hstore blob，我们为每个跟踪的用户维护一个已完成事件的PostgreSQL数组，并将这些事件按时间排序。 Hstore能够让我们以灵活的方式附加属性到事件中，而且事件数组赋予了我们强大的性能，特别是对于漏斗查询，在这些查询中我们计算不同转化渠道步骤间的输出。
　　在这篇文章中，我们那些意外接受大量输入的PostgreSQL函数，然后以高效，惯用的方式重写它。
　　你的第一反应可能是将PostgreSQL中的数组看做像C语言中对等的类似物。你之前可能用过变换阵列位置或切片来操纵数据。不过要小心，在PostgreSQL中不要有这样的想法，特别是数组类型是变长的时，比如JSON、文本或是hstore。如果你通过位置来访问PostgreSQL数组，你会进入一个意想不到的性能暴跌的境地。
　　这种情况几星期前在Heap出现了。我们在Heap为每个跟踪用户维护一个事件数组，在这个数组中我们用一个hstore datum代表每个事件。我们 有一个导入管道来追加新事件到对应的数组。为了使这一导入管道是幂等的，我们给每个事件设定一个event_id，我们通过一个功能函数重复运行我们的事 件数组。如果我们要更新附加到事件的属性的话，我们只需使用相同的event_id转储一个新的事件到管道中。
　　所以，我们需要一个功能函数来处理hstores数组，并且，如果两个事件具有相同的event_id时应该使用数组中最近出现的那个。刚开始尝试这个函数是这样写的：
　　-- This is slow, and you don't want to use it! -- -- Filter an array of events such that there is only one event with each event_id. -- When more than one event with the same event_id is present, take the latest one. CREATE OR REPLACE FUNCTION dedupe_events_1(events HSTORE[]) RETURNS HSTORE[] AS $$ SELECT array_agg(event) FROM ( -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id. SELECT event FROM ( -- Rank elements with the same event_id by position in the array, descending. SELECT events[sub] AS event, sub, rank() OVER (PARTITION BY (events[sub] -& 'event_id')::BIGINT ORDER BY sub DESC) FROM generate_subscripts(events, 1) AS sub ) deduped_events WHERE rank = 1 ORDER BY sub ASC ) to_ $$ LANGUAGE SQL IMMUTABLE;
　　这样奏效，但大输入是性能下降了。这是二次的，在输入数组有100K各元素时它需要大约40秒!
　　这个查询在拥有2.4GHz的i7CPU及16GB Ram的 pro上测得，运行脚本为：/drob/9180760。
　　在这边究竟发生了什么呢? 关键在于PostgreSQL存贮了一个系列的hstores作为数组的值, 而不是指向值的指针. 一个包含了三个hstores的数组看起来像
　　{“event_id=&1,data=&foo”, “event_id=&2,data=&bar”, “event_id=&3,data=&baz”}
　　相反的是
　　{[pointer], [pointer], [pointer]}
　　对于那些长度不一的变量, 举个例子. hstores, json blobs, varchars,或者是 text fields, PostgreSQL 必须去找到每一个变量的长度. 对于evaluateevents[2], PostgreSQL 解析从左侧读取的事件直到读取到第二次读取的数据. 然后就是 forevents[3], 她再一次的从第一个索引处开始扫描,直到读到第三次的数据! 所以, evaluatingevents[sub]是 O(sub), 并且 evaluatingevents[sub]对于在数组中的每一个索引都是 O(N2), N是数组的长度.
　　PostgreSQL能得到更加恰当的解析结果, 它可以在这样的情况下分析该数组一次. 真正的答案是可变长度的元素与指针来实现，以数组的值, 以至于,我们总能够处理 evaluateevents[i]在不变的时间内.
　　即便如此，我们也不应该让PostgreSQL来处理，因为这不是一个的查询。除了generate_subscripts我们可以用unnest，它解析数组并返回一组条目。这样一来，我们就不需要在数组中显式加入索引了。
　　-- Filter an array of events such that there is only one event with each event_id. -- When more than one event with the same event_id, is present, take the latest one. CREATE OR REPLACE FUNCTION dedupe_events_2(events HSTORE[]) RETURNS HSTORE[] AS $$ SELECT array_agg(event) FROM ( -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id. SELECT event FROM ( -- Rank elements with the same event_id by position in the array, descending. SELECT event, row_number AS index, rank() OVER (PARTITION BY (event -& 'event_id')::BIGINT ORDER BY row_number DESC) FROM ( -- Use unnest instead of generate_subscripts to turn an array into a set. SELECT event, row_number() OVER (ORDER BY event -& 'time') FROM unnest(events) AS event ) unnested_data ) deduped_events WHERE rank = 1 ORDER BY index ASC ) to_ $$ LANGUAGE SQL IMMUTABLE;
　　结果是有效的，它花费的时间跟输入数组的大小呈线性关系。对于100K个元素的输入它需要大约半秒，而之前的实现需要40秒。
　　这实现了我们的需求：
　　一次解析数组，不需要unnest。
　　按event_id划分。
　　对每个event_id采用最新出现的。
　　按输入索引排序。
　　教训：如果你需要访问PostgreSQL数组的特定位置，考虑使用unnest代替。
　　我们希望能够避免失误。有任何意见或其他PostgreSQL的秘诀请@heap。
　　[1]特别说明一下，我们使用一个名为Citus Data的贴心工具。更多内容在另一篇博客中!
　　[2]参考：/features/funnels。特别说明一下，计算转换程序需要对用户已完成事件的数组进行一次扫描，但不需要任何join。
[ 责任编辑：jj ]
去年，手机江湖里的竞争格局还是…
甲骨文的云战略已经完成第一阶段…
软件信息化周刊
比特软件信息化周刊提供以数据库、操作系统和管理软件为重点的全面软件信息化产业热点、应用方案推荐、实用技巧分享等。以最新的软件资讯，最新的软件技巧，最新的软件与服务业内动态来为IT用户找到软捷径。
商务办公周刊
比特商务周刊是一个及行业资讯、深度分析、企业导购等为一体的综合性周刊。其中，与中国计量科学研究院合力打造的比特实验室可以为商业用户提供最权威的采购指南。是企业用户不可缺少的智选周刊！
比特网络周刊向企业网管员以及网络技术和产品使用者提供关于网络产业动态、技术热点、组网、建网、网络管理、网络运维等最新技术和实用技巧，帮助网管答疑解惑，成为网管好帮手。
服务器周刊
比特服务器周刊作为比特网的重点频道之一，主要关注x86服务器，RISC架构服务器以及高性能计算机行业的产品及发展动态。通过最独到的编辑观点和业界动态分析，让您第一时间了解服务器行业的趋势。
比特存储周刊长期以来，为读者提供企业存储领域高质量的原创内容，及时、全面的资讯、技术、方案以及案例文章，力求成为业界领先的存储媒体。比特存储周刊始终致力于用户的企业信息化建设、存储业务、数据保护与容灾构建以及数据管理部署等方面服务。
比特安全周刊通过专业的信息安全内容建设，为企业级用户打造最具商业价值的信息沟通平台，并为安全厂商提供多层面、多维度的媒体宣传手段。与其他同类网站信息安全内容相比，比特安全周刊运作模式更加独立，对信息安全界的动态新闻更新更快。
新闻中心热点推荐
新闻中心以独特视角精选一周内最具影响力的行业重大事件或圈内精彩故事，为企业级用户打造重点突出，可读性强，商业价值高的信息共享平台；同时为互联网、IT业界及通信厂商提供一条精准快捷，渗透力强，覆盖面广的媒体传播途径。
云计算周刊
比特云计算周刊关注云计算产业热点技术应用与趋势发展，全方位报道云计算领域最新动态。为用户与企业架设起沟通交流平台。包括IaaS、PaaS、SaaS各种不同的服务类型以及相关的安全与管理内容介绍。
CIO俱乐部周刊
比特CIO俱乐部周刊以大量高端CIO沙龙或专题研讨会以及对明星CIO的深入采访为依托，汇聚中国500强CIO的集体智慧。旨为中国杰出的CIO提供一个良好的互融互通 、促进交流的平台，并持续提供丰富的资讯和服务，探讨信息化建设，推动中国信息化发展引领CIO未来职业发展。
IT专家新闻邮件长期以来，以定向、分众、整合的商业模式，为企业IT专业人士以及IT系统采购决策者提供高质量的原创内容，包括IT新闻、评论、专家答疑、技巧和白皮书。此外，IT专家网还为读者提供包括咨询、社区、论坛、线下会议、读者沙龙等多种服务。
X周刊是一份IT人的技术娱乐周刊，给用户实时传递I最新T资讯、IT段子、技术技巧、畅销书籍，同时用户还能参与我们推荐的互动游戏，给广大的IT技术人士忙碌工作之余带来轻松休闲一刻。
微信扫一扫
关注Chinabyte}