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://*.com/questions/26877241/query-combinations-with-nested-array-of-records-in-json-datatype/26880705#26880705
--https://blog.csdn.net/luojinbai/article/details/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.
*/