Filter a query on a JSON field

Wappler General Need Help
7

I have worked on my table “products” that contains a json column (prd_Tags).

  1. I pull all the records of my products table (NO OUTPUT)
  2. Create an arrayList and set the value to my step-1 query
    image
    image1498×745 39.9 KB
  3. Clear the arrayList (I just wanted to copy the schema, we don’t need the data)
  4. I add a repeat with expresion the step-1 query (loop throught the table’s products)
  5. Set Value tags_Array and gave it the current repeat’s prd_Tags column value
  6. I create a switch and set it to false (this is to check if the record is already added to the arrayList)
  7. I add inside the repeat one more repeat to repeat through each product’s tags (JSON)
    (here I explain that you could skip step 5 and set now the repeat straight to the prd_Tags column, but just like to see the setValue steps and know what I’m doing)
  8. I add a condition to check when to add the current product to the arrayList.
    If switch == false (the product has not been already added to arrayList) and $_GET.filtertags,contains($value) (filter contains the current product’s tag)
  9. if the conditions are met add the current repeat’s product to the arrayList
    image
    image1499×984 51.4 KB
  10. set the switch to true in order not to add again the same product
  11. Closing all all repeats, we get the arrayList and this is the final filtered query that we are going to use client-side to list our products
    (THIS IS THE ONLY STEP/VALUE THAT HAS OUTPUT ticked)

Now I realized that if no filter is set the arrayList(new query) will be empty…

You can avoid this and return all the records of the table if you add a condition after step-2 and check if filter has value.
Enclose all steps 4-10 inside the if and leave step-11 outside the if statement (so we have in both cases the arrayList to list our records client-side)

I hope it helps

1 Like