support json search

[clysss]

hello @mevdschee , Happy new year 2025 !
with posgresql, I've a field name data (character varying) containing a json.
suppose this json is {"xxx":"yyy", "zzz":{"www":"sss", "tags":["one", "two", "three"]}}.
I can write
SELECT * FROM public.xxx
WHERE "data"::jsonb ->'zzz'-> 'tags' @> '"two"';
to have the records having tags=two
I'm using a custom controller. Is there a way to make a call to recordService->_list(
with some parameters to create this SQL call ?

I haven't see anything about json select in json middleware and begin to deep into
https://github.com/mevdschee/php-crud-api/blob/main/src/Tqdev/PhpCrudApi/Database/ConditionsBuilder.php

my goal could be something like
GET /records/your_table?filter=data->zzz->tags,js,two
[in fact I just need in my case [filter =>"data->zzz->tags,js,two"] (or even ""data"::jsonb ->'zzz'-> 'tags',js,two )
or perhaps if its easier "data, js,zzz->tags:two"
js would be a new keyword if needed to allow "fake" colum name ("data->zzz->tags") : what do you think about that ??
Thx

[mevdschee]

Hi Clysss

what do you think about that ??

My honest opinion? I think storing unstructured data in a RDBMS is silly (especially if you want to search that data).

I have explored this avenue before and if we were targeting only one database system, then this would be feasible. With support for many underlying databases this will be very difficult.

Maybe a workaround for your situation is that you create a view that represents the nested JSON property in a (nullable) column, as reading from views and searching data in views is supported.

Kind regards,

Maurits

[clysss]

Yes, I'm only targeting postgresql and I've implemented it through views currently. It works well:) And in one case, I used a custom SQL through pdo ->query to make an update using a formula. If you don't consider it's a good idea to implement something more in phpcrudapi, no direct need to me : I'm perfectly fine with my custom ctrl! Thx for your point of view Regards Le sam. 15 févr. 2025, 21:26, Maurits van der Schee < ***@***.***> a écrit :

…

Hi Clysss what do you think about that ?? My honest opinion? I think storing unstructured data in a RDBMS is silly. I have explored this avenue before and if we were targeting only one database system, then this would be feasible. With support for many underlying databases this will be very difficult. Maybe a workaround for your situation is that you create a view that represents the nested JSON property in a (nullable) column, as views are supported. Kind regards, Maurits — Reply to this email directly, view it on GitHub <#1049 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANKVWKXFEIXZVJ6MUUWD5NL2P6PGHAVCNFSM6AAAAABU4VHH7SVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRRGA4DSMBQHE> . You are receiving this because you authored the thread.Message ID: ***@***.***> [image: mevdschee]*mevdschee* left a comment (mevdschee/php-crud-api#1049) <#1049 (comment)> Hi Clysss what do you think about that ?? My honest opinion? I think storing unstructured data in a RDBMS is silly. I have explored this avenue before and if we were targeting only one database system, then this would be feasible. With support for many underlying databases this will be very difficult. Maybe a workaround for your situation is that you create a view that represents the nested JSON property in a (nullable) column, as views are supported. Kind regards, Maurits — Reply to this email directly, view it on GitHub <#1049 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANKVWKXFEIXZVJ6MUUWD5NL2P6PGHAVCNFSM6AAAAABU4VHH7SVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRRGA4DSMBQHE> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[mevdschee]

For completeness I reference the (reverted) issue: #176