0

I can't seem to able to wrap my head around this.

I got several translations in a json column called text, and I want to select only those records that have not empty translations for a specific language (not set, or not null).

I struggle in creating a query that doesn't return a record if the value is set to null. Here are some text column json values, and I need a query that returns only the first record in this example, where I have a translation for the fr value:

text: "{"de": "danke", "fr": "merci"}"
text: "{"de": "hallo", "fr": null}"
text: "{"de": "und"}"

Here is what I tried so far:

parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") is not null' )->get();

-> returns 

text: "{"de": "danke", "fr": "merci"}"
text: "{"de": "hallo", "fr": null}"

Bummer. It skips the record where fr is not set at all (that's ok), but keeps selecting the record where fr is set to null. So, I need to adjust the query to exclude the record with "fr": null

Let's approach this step by step. When I try a positive match search for a string like this:

parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") = "merci"' )->get();

-> returns

text: "{"de": "danke", "fr": "merci"}"

That is correct. Now, it would seem easy to find a way to switch this around and exclude records with "fr": null. But no luck. 

parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") <> "merci"' )->get();

-> returns only the record where fr is set, but is not equal to merci. It skips the record where fr is not set.

text: "{"de": "hallo", "fr": null}"

Let's try to exclude records where fr is set to null. But no luck either:

parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") <> "null"' )->get();

-> returns

text: "{"de": "danke", "fr": "merci"}"
text: "{"de": "hallo", "fr": null}"

Or,

parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") <> null' )->get();

-> returns an empty result.

How the heck can I exclude fr values that are not set or are set to null?

Aleksandar
  • 1,496
  • 1
  • 18
  • 35
  • this is not an answer but, if you're using laravel you may want to use laravel localization https://laravel.com/docs/5.6/localization – Teoman Tıngır Sep 16 '18 at 09:39
  • @HasanTıngır: I am using the spatie/laravel-translatable package and I need to filter the translations. – Aleksandar Sep 16 '18 at 09:40
  • using that serialize, unserialize methods in each query, slow down your application. I think creating trans table way much better, faster and easy to handle. https://imgur.com/a/EsdpBEh – Teoman Tıngır Sep 16 '18 at 09:49

1 Answers1

1

I found a solution in a similar Stackoverflow thread. It is not a very obvious issue I would say for someone with beginner knowledge.

It seems that the null value in the json column doesn't evaluate to the same null value in my previous query attempts. It needs to be adjusted as follows, and then it works. Basically the null value needs first to be changed to the same null value type with the following code:

CAST("null" AS JSON)

And then fit it into the query: 

parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") <> CAST("null" AS JSON)' )->get();
Aleksandar
  • 1,496
  • 1
  • 18
  • 35
  • hi how can i put OR condition where not NULL or '' (empty string) i ahve tried <> CAST("null" AS JSON), <> CAST("''" AS JSON) but its not working.. i want to return values that are not null or empty string inside of json part. It is possible? Im confused also on the stackoverlfow thread how to do it – Winston Fale Jan 28 '20 at 06:56