Postgrest: query on json

[andelo]

How do you select on json attribute/value ?

http://127.0.0.1:3000/test?data->>fullname=eq.Davy%20Jones

produces

{"status":"Fatal error","hint":null,"code":"42703","message":"column \"data->>fullname\" does not exist"}

[ludwigschubert]

From what I get so far you don't use data->>. In your example you would simply request

http://127.0.0.1:3000/test?fullname=eq.Davy%20Jones

You can try https://postgrest.herokuapp.com/sessions?location=eq.The%20Gym live.

@begriffs _Fascinating_ project.

[andelo]

data is the name of a column containing a json object {fullname:"string",...}
I think that json operators are not supported, would be nice to have though.

[ludwigschubert]

@andelo thanks for the clarification. In this case this question is a little over my head, but from comparing the PostgreSQL documentation and the opCode matching statement I'd guess the JSON query operators are not yet supported.

I just discovered this project an hour ago, though, so hopefully @begriffs could chime in.

[PierreR]

:+1: for adding jsonb support

[begriffs]

Sure, I can add support for ->.

One question though, have you considered normalizing the data so it is stored in multiple columns rather than as JSON within a single column?

[andelo]

In a real scenario I would create a more robust data model.
But for my test code, no.

[adambaker]

There currently are many, many options that postgresql gives for filtering the results of a query that are not supported by postgrest. To some degree this is because this is a minimal viable product release, with more features on the way. For instance, we definitely want to make it easy to include a IN clause in a GET/PATCH request, eg:
GET /items?id=in.[1,3,11,24]
should generate:
select * from items where id in [1,3,11,24]
(but with the query properly parametrized, of course.)

Support for schemaless columns like this is an advanced feature that to some extent runs against the philosophy of postgrest: use the database to model your application domain as much as possible. It'd be a cool feature to have, but I'm unlikely to give it priority. If someone were gracious enough to send us a pull request implementing this, I wouldn't turn it down...

Here's another guiding philosophy we have: If you need to use very complex filters that aren't expressible in the simple query language we provide, you should create a view for it instead. That's not so helpful in your test/exploration code use case though, and I do think that's a case we want to support.

[begriffs]

@PierreR can you tell me more about your use-case for using the postgres json column operations? It seems like it certainly has its uses but I'm trying to prioritize between this and other features.

[PierreR]

At my work Postgresql is an emerging standard. We are really interested in using the new jsonb feature for a couple of projects. I really would like to propose postgrest and I suspect that jsonb support would be a real plus.

Then I will need that feature for a personal project of mine later on this year.

So nothing urgent really. I will keep an eye on the project and hope to make use of it quite soon.

Thanks for your support. postgrest looks amazing !

[begriffs]

@PierreR I've just finished some other features and can take a look at the jsonb support soon. Any plans to try postgrest for your personal project?

[PierreR]

I would love to say yes but I am quite busy with other things ;-)

Following the spirit of this article I would start by implementing the "read" (query) feature.

Hope this is helpful.

Cheers,

[thorstenz]

@begriffs fascinating project!

jsonb support with --> access and
ability to query with the in clause
would be great.

[arunnairmr]

@begriffs Did you implement this feature? If yes give me a sample get API. I am still getting the same error as above.
And I am getting error when inserting json data.

http://localhost:3000/person

{
"data": {
"Fname": "xxx",
"Lname": "yyy"
}
}

{"message":"Failed to parse JSON payload. Failed reading: satisfy"}

[begriffs]

Sorry I haven't implemented it yet.

[arunnairmr]

ok. Can you implement it fast ? we get stuck on this problem.

[begriffs]

@arunnairmr would next Monday be too late for you? During this week I'm busy with some other things but I can add this issue to my weekend todos.

[arunnairmr]

@begriffs OK. we are waiting for your new update.all the best.