SQL Queries

SELECT concat('{"id":"',uid_prispevok,'"}', E'\n', row_to_json(prispevok_import)) FROM prispevok_import ORDER BY uid_prispevok LIMIT 10


SELECT concat('{ "index" : { "_index" : "test_news", "_type" : "test_news", "_id":',uid_prispevok,'}}', E'\n', row_to_json(prispevok_import)) FROM prispevok_import ORDER BY uid_prispevok LIMIT 10;


{ "index" : { "_index" : "test_news", "_type" : "test_news", "_id" : "1" } }




{ "index" : { "_index" : "test", "_type" : "type1", "_id" : "1" } }
{ "field1" : "value1" }



OK : psql inside

SELECT concat('{ "index" : { "_index" : "test_news", "_type" : "test_news", "_id":',uid_prispevok,'}}', E'\n', row_to_json(prispevok_import)) FROM prispevok_import ORDER BY uid_prispevok ;


OK : cmd bash

psql -U newsadmin -d test_news -c 'SELECT row_to_json(t) FROM prispevok_import as t LIMIT 1000' > 2.json

# SEARCH AND JOIN through 2 TABLES
SELECT z_id, z_name, urlid, cpv FROM `zakazky` JOIN `zakazky_cpv` USING (urlid) WHERE `cpv` = "60000000-8"
SELECT z_id, z_name, urlid FROM `zakazky` JOIN `zakazky_watcher` USING (urlid) WHERE `watcher_id` = "2"
SELECT z_id, z_name, urlid FROM `zakazky` JOIN `zakazky_watcher` USING (urlid) WHERE `watcher_id` = "2"

SELECT zakazky.*, (SELECT 'watching' FROM zakazky_watcher WHERE zakazky_watcher.urlid=zakazky.urlid AND watcher_id='$userid' LIMIT 1) AS im_watching FROM zakazky ORDER BY urlid DESC LIMIT 450

SELECT * FROM Users_Reminders WHERE z_deadline < NOW() + INTERVAL 1 HOUR