PG Query Errors to Convert Json to Tabular Format for Analysis

Technical Support
1

Colleagues, pulled data from couchdb for analysis, now want to pull some data about the enumararors, clinicians in our usecase from the couchdb table, meta data group which has the name of the person who logged in, their place and person uuids as below:
“meta”: {“created_by”: “name_person”,
“created_by_place_uuid”: “772c1114-125d-4c6c-85ca-75eefd2f1941”,
“created_by_person_uuid”: “33db2295-0fc2-4362-88bf-39a17c5d7587”},

My query is as below, but doesn’t return results:
SELECT couchdb.doc ->> ‘_id’::text AS uuid,
couchdb.doc ->> ‘form’::text AS form,
couchdb.doc #>> ‘{fields,_id}’::text AS _id,
couchdb.doc #>> ‘{fields,meta,created_by}’::text AS created_by,
couchdb.doc #>> ‘{fields,meta,created_by_place_uuid}’::text AS created_by_place_uuid,
couchdb.doc #>> ‘{fields,meta,created_by_person_uuid}’::text AS created_by_person_uuid,
couchdb.doc #>> ‘{fields,parent}’::text AS parent,
FROM couchdb,
WHERE (couchdb.doc ->> ‘form’::text) = ‘case_investigation’::text;

I have seen the same data under the table couchdb_medic_users, but my queries aren’t running to extract data so that I combine with CIF data, which I have pulled and converted for analysis, any ideas why?

2

Hi Philip,

Does the query run and not return results as expected or does it fail with an error? When I tried the same query I got some syntax errors. I changed it to the one below which successfully ran.

SELECT
    couchdb.doc ->> '_id' AS uuid,
    couchdb.doc ->> 'form' AS form,
    couchdb.doc #>> '{fields,_id}' AS _id,
    couchdb.doc #>> '{fields,meta,created_by}' AS created_by,
    couchdb.doc #>> '{fields,meta,created_by_place_uuid}' AS created_by_place_uuid,
    couchdb.doc #>> '{fields,meta,created_by_person_uuid}' AS created_by_person_uuid,
    couchdb.doc #>> '{fields,parent}' AS parent
FROM couchdb
WHERE couchdb.doc ->> 'form' = 'case_investigation';

My updated query has the following changes:

  1. Replaced curly quotes with straight quotes.
  2. Removed redundant ::text casts.
  3. Removed the extra comma before the FROM clause.
  4. Removed the comma after couchdb in the WHERE clause.
1 Like
3

@njuguna, the query runs but doesn’t display data as expected.

I tried another way, create two materialized views, one for the CIF, which works and the clinician details, which works on its own, but when you do a join condition between the CIF and clinician queries, doesn’t display the data as required;
select *
from userview_cif_all, userview_clinician_detailsv2
where userview_cif_all.uuid = userview_clinician_detailsv2.p_uuid

The query returns nill values, while running
select *
from userview_cif_all returns 1729 records while running

select *
from userview_clinician_detailsv2 returns 22022, and this includes data about hierarchy places, users and clinicians.
So exploring how to combine data from the two forms into one and only pick patient details, so analysis makes sense, any ideas?

4

From this statement I think you need to do a UNION and not a JOIN in your query.

This query will return records that have matching values in both tables which in this case there are none thus the empty result.

5

@njuguna, I checked the joining fields and noted I have patient_uuid and p_uuid, I made a mistake with the first query and used uuid, the reason I didn’t get results.
Thus the query below has run and produced the required results:

FROM userview_cif_all
LEFT JOIN userview_clinician_detailsv2 ON userview_clinician_detailsv2.p_uuid = userview_cif_all.patient_uuid;

2 Likes