Postgresql - jsonb_pretty & dateStyle

來源:互聯網
上載者:User

標籤:hat   str   static   com   error   creat   new   cts   different   

1.

SHOW datestyle;

DateStyle
-----------
ISO, MDY
(1 row)

INSERT INTO container VALUES (‘13/01/2010‘);
ERROR: date/time field value out of range: "13/01/2010"
HINT: Perhaps you need a different "datestyle" setting.

SET datestyle = "ISO, DMY";
SET

INSERT INTO container VALUES (‘13/01/2010‘);
INSERT 0 1

SET datestyle = default;
SET

------------------------------------------------

2.


--http://www.silota.com/docs/recipes/sql-postgres-json-data-types.html
--https://www.postgresql.org/docs/9.3/static/functions-json.html
--https://stackoverflow.com/questions/26877241/query-combinations-with-nested-array-of-records-in-json-datatype/26880705#26880705
--45091839

DROP TABLE IF EXISTS reports;

CREATE TABLE reports (rep_id int primary key, data json);

TRUNCATE TABLE reports;

INSERT INTO reports (rep_id, data)
VALUES
(1, ‘{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barB.png", "pos": "top"}], "background":"background.png"}‘)
, (2, ‘{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barC.png", "pos": "top"}], "background":"bacakground.png"}‘)
, (3, ‘{"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}‘)
, (4, ‘{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 3, "src":"barB.png", "pos": "top"}], "background":"backgroundA.png"}‘)
;

/*1. example 1 */
/*
SELECT array_agg(r.rep_id) AS ids, count(*) AS ct
FROM reports r
, json_array_elements(r.data->‘objects‘) o

where o->>‘pos‘ in (‘top‘,‘fooC.png‘)

GROUP BY r.data->>‘background‘
, o->>‘album‘
, o->>‘scr‘
ORDER BY count(*) DESC
LIMIT 3;
*/

/* 2. example 2 is what we wanted.*/

select distinct on(rep_id)r.rep_id AS id2,r.data,r.data->‘objects‘ objects
FROM reports r
, json_array_elements(r.data->‘objects‘) o

where o->>‘pos‘ in (‘top‘,‘middle2222‘)
ORDER BY r.rep_id DESC

/* 3. format output json string */

/*
select jsonb_pretty( ‘{"name": "Alice", "agent": {"bot": true} }‘::jsonb );
-- returns the following
{
"name": "Alice",
"agent": {
"bot": true
}
}
*/

/* 4. update
update sales set info = info || ‘{"country": "Canada"}‘;

Use the || operator to concatenate existing data with new data.
The operator will either update or insert the key to the existing document.

*/

 

Postgresql - jsonb_pretty & dateStyle

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.