Filter string keys of array from a string in a Javascript object format in an SQL table

Question

I would like to filter out keys (given in an array of strings - ["key2"]) in a given column on an SQL table, say:

{ key1: val1, key2: val2, key3: val3 }

 |--row---|-object--|---value----|

 |---1----|--obj1---|--sum(obj1)-|

 |---2----|--obj2---|--sum(obj2)-|

 |---3----|--obj3---|--sum(obj3)-|

I want to be able to do this with the use of SQL and filter all keys of an array for each row in parallel.

So for example, if I had these objects:

obj1 = "{ key1: val1, key2: val2, key3: val3 }"

obj2 = "{ key1: val1, key2: val2 }"

obj3 = "{ key1: val1, key2: val2, key3: val3, key4: val4, key5: val5 }"

and an array of ["key1", "key4"]

I would get new table with new objects and new values as:

obj1 = "{ key2: val2, key3: val3 }" , val = val2+val3

obj2 = "{ key2: val2 }" , val = val2

obj3 = "{ key2: val2, key3: val3, key5: val5 }" , val = val2+val3+val5

Is it possible?

I tried to use JSON_EXTRACTOR in order to extract the object as a JSON format and manipulate the rows with the filter function one by one but it is not very efficient when I think about it, and it is not using the query to do the filtering.

Found a solution using JSON_EXTRACT: dynamically append " JSON_EXTRACT(object, '$.obj1') as obj1, " strings to a SELECT. * about the efficiency - I don't know yet if it is the best solution and if it is scalable. — Talker Almog
– Talker Almog, Commented Feb 19, 2020 at 14:43

Joss Baron · Accepted Answer · 2020-02-19 16:04:22Z

0

as per my understanding you are using MySQL. The way that you have found I think is the proper, according to this article about JSON_EXTRACT() points that:

SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": 3}', '$.b') AS 'Result';

Here you can find the official documentation.

You can always use the Query Execution Plan to verify the efficiency of your queries, but it also depend on your tables, columns, indexes and the conditions in the Where clause.

answered Feb 19, 2020 at 16:04

Joss Baron

1,52410 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter string keys of array from a string in a Javascript object format in an SQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related