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
orjson
.Maybe I don't understand your question
Then just do..
SELECT * FROM table WHERE job_id = 1
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...