Postgres issue using json_populate_recordset function

Question

Here I have created a type to use json_populate_recordset function .

CREATE TYPE Visits_type AS
    (facilityId varchar,patientId varchar,encounterId numeric,patientClass varchar,admnDate varchar,dischargeDate varchar,practitionerId varchar,
     careLocationType varchar,careLocationCode varchar,specialtyCode varchar,status varchar,admitPractitionerId varchar,hpSeqNo varchar,admissionType varchar,assignedRoom varchar,assignedBed varchar,
     Firstname varchar,
     Patientname varchar,
     Firstnamelocal varchar,
     Patientnamelocal varchar,
     sex varchar,
     Dob varchar,
     Mobilenumber varchar,
     Emailid varchar,
     RegnFacilityid varchar);

following Sample sql and json is used:

SELECT * from json_populate_recordset(null::Visits_type, '[
  {
    "facilityId": "10","patientId": "GH00000940","encounterId": 100011770001,"patientClass": "OP","admnDate": "2015-05-17 17:25","dischargeDate": null,"practitionerId": "ALLEN","careLocationType": "C","careLocationCode": "CL11","specialtyCode": "SP11",   "status": "E",    "admitPractitionerId": null,    "hpSeqNo": 3796,    "admissionType": "First Visit",    "assignedRoom": "R1",    "assignedBed": null,    "firstName": "Suresh",    "patientName": "Suresh    Machwal",    "firstNameLocal": null,    "patientNameLocal": null,    "sex": "M",    "dob": "1970-05-17",    "mobileNumber": null,    "emailId": null,    "regnFacilityId": "GH"  },  {
    "facilityId": "10",
    "patientId": "GH00000940",
    "encounterId": 100011770001,
    "patientClass": "OP",
    "admnDate": "2015-05-17 17:25",
    "dischargeDate": null,
    "practitionerId": "ALLEN",
    "careLocationType": "C",
    "careLocationCode": "CL11",
    "specialtyCode": "SP11",
    "status": "E",
    "admitPractitionerId": null,
    "hpSeqNo": 3795,
    "admissionType": "First Visit",
    "assignedRoom": "R1",
    "assignedBed": null,
    "firstName": "Suresh",
    "patientName": "Suresh    Machwal",
    "firstNameLocal": null,
    "patientNameLocal": null,
    "sex": "M",
    "dob": "1970-05-17",
    "mobileNumber": null,
    "emailId": null,
    "regnFacilityId": "GH"
  }]');

The issue is that its returning null values for all the fields, except for then dob, status and sex.

i can see the actual values when i query.. but when the use FOR I IN (SELECT * FROM json_populate_recordset(null::visits_type,visit_info->'visits')) LOOP .. I get error i.patientID field is not available. I is of TYPE visit_type which i created with double quotes — Chandrashekhar BC
– Chandrashekhar BC, Commented Apr 29, 2016 at 6:51

Clément Prévost · Accepted Answer · 2016-04-29 06:37:28Z

4

You should be using double quotes when you create your type to keep your original case:

CREATE TYPE Visits_type AS
    ("facilityId" varchar,
     "patientId" varchar,
     "encounterId" numeric,
     "patientClass" varchar,
     "admnDate" varchar,
     "dischargeDate" varchar,
     "practitionerId" varchar,
     "careLocationType" varchar,
     "careLocationCode" varchar,
     "specialtyCode" varchar,
     "status" varchar,
     "admitPractitionerId" varchar,
     "hpSeqNo" varchar,
     "admissionType" varchar,
     "assignedRoom" varchar,
     "assignedBed" varchar,
     "Firstname" varchar,
     "Patientname" varchar,
     "Firstnamelocal" varchar,
     "Patientnamelocal" varchar,
     "sex" varchar,
     "Dob" varchar,
     "Mobilenumber" varchar,
     "Emailid" varchar,
     "RegnFacilityid" varchar);

answered Apr 29, 2016 at 6:37

Clément Prévost

8,6702 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres issue using json_populate_recordset function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related