Postgresql – Help writing a query to transform rows with json data into 1 download file

postgresql

I want to write a query for a table like this:

id  job_id  data (bytea)
--------------------------
 1       1  {"hello": "John"}
 2       1  {"goodbye": "Moon"}
 ...
100      2  {"also": "Hi"}

I want to be able to ask:

> Give me all the data for job #1

and have the text file automatically built like this:

{"hello": "John"}
{"goodbye": "Moon"}

My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it. This seems to work:

    select encode(data, 'escape') from mytable where id='1'

But I would rather not do 2 queries (one to get all rows – and another to get data). Can I issue a single query, and somehow paginate, one row at a time? Number of rows can be large, e.g. 100k.

Any tips to point me in the right direction are much appreciated.

P.S.: Order is not that important to me. E.g. this is ok too (if this helps):

{"goodbye": "Moon"}
{"hello": "John"}

Best Answer

Don't store JSON as bytea. Store it as jsonb or json.

Maybe I don't understand your question

ALTER TABLE table
  ALTER COLUMN data
  SET DATA TYPE jsonb USING encode(data, 'escape')::jsonb;

Then just do.. SELECT * FROM table WHERE job_id = 1

My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it.

Why? What does too large mean there? The way this works, you'll only encode one row at a time and send it to the client...

Related Question