New answers tagged json
7
Hope you're doing great.
I would like to see your schema to make sure all is good.
I tried to recreate what I believe you did.
Here is what I did:
mysql> create database test295895;
mysql> use test295895;
mysql> create table my_table (my_column JSON);
Then I inserted the values you showed:
mysql> insert into my_table VALUES('{"my_value"...
1
JSON_QUERY with no path is used to prevent escaping of valid JSON strings. You need to place this directly within the JSON_MODIFY call for it to work properly, you cannot place it outside of the function parameter.
This is pointed out in the documentation:
JSON_MODIFY escapes all special characters in the new value if the type of the value is NVARCHAR or ...
5
You should directly extract the values from the json array using the WITH clause, like this:
SELECT src.*
, tt.*
FROM json_test src
CROSS APPLY OPENJSON(src.the_json , 'lax $')
WITH (
[name] sysname '$.name'
, [object_id] int '$.object_id'
, [principal_id]...
0
You need to move the ? filter onto the observations node, and within that, filter on value.
Also, I think you want 22.0 without quotes, because it is comparing to a double
SELECT jsonb_path_query(
series_data_point,
'$.observations ? (@.value[*].double() >= 22.0)') as value
from table
0
As has been commented, you don't have to list target columns for json(b)_populate_record(). While using the same row type, columns are guaranteed to match.
Plus, I would simplify / optimize a bit:
CREATE OR REPLACE FUNCTION appl.insert_fund_overview(_sym text, _ex text, _js jsonb) -- ②
RETURNS void
LANGUAGE plpgsql AS
$func$
DECLARE
_sym_id jsonb;
...
0
Any ideas of how to dynamically create and fill multiple session temp tables at different locations in the same run?
Dynamic queries and stored procedure invocations are both "nested scopes". Existing temp are visible in nested scopes, but temp tables created in nested scopes are automatically dropped at the end of the nested scope.
So the ...
1
If every row has the same two attributes in JSON, you should consider making those into table columns (date and value). That will make the subsequent SQL much simpler.
1
You can use jsonb_path_query_array to get all matching array elements:
jsonb_path_query_array(data, '$.board_members[*] ? (@.ind == true)')
The above returns
[
{"ind": true, "last_name": "Grant", "first_name": "Hugo"},
{"ind": true, "last_name": "Flair", "first_name&...
0
You could use JSON_EXTRACT
The SQL code would look something like this:
SELECT SUM(JSON_EXTRACT(json_field, '$.value')) FROM table WHERE JSON_EXTRACT(json_field, '$.date') REGEXP '2021-06-28|2021-06-27';
Top 50 recent answers are included
