CODEWITHSUNDEEP
sqlpostgresql
user5740953
user5740953

Reputation: 179

postgresql filter data from bytea column

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"
}

Upvotes: 0

Views: 2322

Answers (1)

w08r
w08r

Reputation: 1804

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';

Upvotes: 2

Related Questions