postgresql filter data from bytea column

Question

I have a table where i am saving data in a column of type bytea, the data is actually a JSON object. I need to implement a filter on the JSON data.

SELECT  cast(job_data::TEXT as jsonb) FROM job_details where job_data ->> "organization" = "ABC";

This query does not work. The JSON Object looks like

{
    "uid": "FdUR4SB0h7",
    "Type": "Reference Data Service",
    "user": "[email protected]",
    "SubType": "Reference Data Task",
    "_version": 1,
    "Frequency": "Once",
    "Parameters": "sdfsdfsdfds",
    "organization": "ABC",
    "StartDateTime": "2020-01-20T10:30:00Z"
}

You should always use the appropriate data type (in this case jsonb) so that your queries are simple and perform well. — Laurenz Albe
– Laurenz Albe, Commented Jan 20, 2020 at 6:51
Why are you storing a JSON value in a bytea column? That's not a good choice. You should use the jsonb or json data type for that. — user330315
– user330315, Commented Jan 20, 2020 at 7:07
it been done by quartz .net scheduler, I need to just query data as per need. — user5740953
– user5740953, Commented Jan 20, 2020 at 22:04

w08r · Accepted Answer · 2020-01-20 06:26:13Z

3

You need to predicate on the converted column, also, that conversion may not necessarily work depending on encoding. Try something like this:

SELECT
    *
FROM
    job_details
WHERE
    convert_from(job_data, 'UTF-8')::json ->> 'organization' = 'ABC';

answered Jan 20, 2020 at 6:26

w08r

1,84415 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

postgresql filter data from bytea column

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