0

i am having some trouble relating some search in the json type field in psql database , here is the case scenario

i have this sample data in the json type field

 {"created_at": "2014-11-09 00:28:50", "id": 6, "operation": "UPDATE", "desc": "DB updation V3_19 "}
 [{"created_at": "2014-11-16 18:21:22", "id": 6, "operation": "UPDATE", "desc": "DB updation V3_19 "}]
 {"created_at": "2015-03-18 08:25:07", "id": 7, "operation": "INSERT", "desc": "DB INSERTION V3_24 "}
 [{"created_at": {"$date": "2014-08-05T00:30:15.843+0500"}, "id": 5, "desc": "replaced from DB"}, {"created_at": "2014-11-17 00:08:42", "id": 6, "operation": "UPDATE", "desc": "DB updation V3_19 "}]
 {"created_at": "2015-03-18 08:25:07", "id": 7, "operation": "INSERT", "desc": "DB INSERTION V3_24 "}
 {"created_at": "2015-03-18 08:25:07", "id": 7, "operation": "INSERT", "desc": "DB INSERTION V3_24 "}

and i want to query only those records having operation = 'INSERT' and desc = 'DB INSERTION V3_24'

but when i am doing this simple query select count(*) from factual where info_flags->>'operation' = 'INSERT' info_flags is the name of the json_type column,

it gives me the error as " ERROR: cannot extract field from a non-object "

can anyone guide me either it is data-inconsistency error or there is problem with query.

thanks

3
  • 1
    it seems that your json values are mixed between objects {} and arrays [] . can you give the result of a SELECT info_flags FROM factual ? Commented Mar 31, 2015 at 9:26
  • here is the values from database of info flags [{"created_at": "2014-11-17 02:29:15", "id": 6, "operation": "UPDATE", "desc": "DB updation V3_19 "}] {"created_at": "2014-11-12 00:48:08", "id": 6, "operation": "UPDATE", "desc": "DB updation V3_19 "} also contains the null values @disf.asia waitng for your response Commented Mar 31, 2015 at 11:37
  • again, it seems that you stored different types in different rows. IE: if the first row contains { object } and the second contains [ { object } ] they cannot be used in the same way. you have to verify your data consistency Commented Mar 31, 2015 at 12:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.