Hi,
It is not possible to query with couch in the same way you can with
postgres. For example you can query the documents with LIKE
'%sometext%' which is very hard to do / takes a long time with temp
views for one off queries.
Also with postgres it is then possible to JOIN documents together - very
simple example:
WITH companies AS (
SELECT id,
doc ->> 'name' AS company_name,
FROM couchdata WHERE doc @> '{"type": "company"}'
),
contacts AS (
SELECT id,
doc ->> 'company_id' AS company_id
doc ->> 'name' AS contact_name
FROM couchdata WHERE doc @> '{"type": "contact"}'
)
SELECT company_name, contact_name FROM companies
LEFT JOIN contacts ON (companies.id=contacts.company_id)
WHERE contact_name ILIKE '%john%'
ORDER BY company_name, contact_name
This would be very hard to do with map/reduce for one off
searches/queries with couch and take a while to build the views when
number of docs gets quite large.
We have used this to replaced Elastic Search with Postgres now for our
apps search needs and are using far less memory/resources compared to ES
and getting rid of java from our main stack is a nice side bonus.
Take a look at: https://github.com/sysadminmike/yadms for some more
examples of how you can use postgres to query the couch data as that
should give you some idea how simple it is to use postgres on the couch
data compared to doing within couch.
Mike.
On 07/03/2016 16:49, Matthew Buckett wrote:
On 4 March 2016 at 12:03, Mike <[email protected]> wrote:
We have a number of client couchdbs which we query as one.
I initially wrote: https://github.com/sysadminmike/couch-to-postgres
To help get ad hoc reports from our couch data using postgres.
Thanks looks interesting. What were the reasons for doing the
reporting in postgres rather than in couchdb?
