How to push/remove a value in the list while editing the record in 1 step?

Dan Kulkov

Hi

I am new to Xano. So this might be a stupid question. But I didn't manage to find a good answer online.

I have a table "files" with column "variants" that is a list of integers.

I want to remove/push the value in this list.

Right now, I need to do 2 steps

1. Get a record to basically get the current list ("variants_id")
2. Edit record (paste the current list from step 1 and then push/remove value from it)

I am sure there is a better way to do it in 1 step.

What am I missing?

Lachlan

Hi there, your approach is right, you need to first get the record so you can update the array before editing the record again.

If you do need to do this in a single line item, you might want to do a direct database query with SQL.

Dan Kulkov

That sounds inefficient (?).

For a very simple delete request, getting a record takes x2 more time than editing it.

Any tips on how to use SQL in this case?

Lachlan

I'd suggest reading up on the documentation here:
https://docs.xano.com/working-with-data/functions/database-requests/direct-database-query

You will need to use the mvpw naming convention for your query:

A rough example of how the query will look: (I haven't tested this)

UPDATE mvpw{workspaceId}_{tableId}

SET xdo = jsonb_set(xdo, '{variants}',

(

SELECT jsonb_agg(value)

FROM jsonb_array_elements_text(xdo->'variants')

WHERE value <> 'your_variant_value'

)

)

WHERE xdo->>'id' = 'your_file_id_value' AND 'your_variant_value' = ANY(ARRAY(SELECT jsonb_array_elements_text(xdo->'variants')));

You would update the 'your_variant_value' & 'your_file_id_value' with the ? statement arguments as outlined in the docs.

This being said, I wouldn't be overly concerned with your current implementation as it is still rather efficient to perform that process. If you do by chance get the SQL function set it'd be great to see the difference in speed.

Dan Kulkov

Thank you, @Lachlan. Appreciate you took time!

Lachlan

No worries @Dan Kulkov , I've just updated the above post as I had left out the File id from the query.