couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike <m...@wolman.co.uk>
Subject Re: Reporting against a CouchDB database
Date Mon, 07 Mar 2016 22:20:26 GMT
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 <mike@wolman.co.uk> 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?
>


Mime
View raw message