New answers tagged json
0
You could see if any existing columns are of the JSON data type (MySQL only) with something like:
SELECT *
FROM columns
WHERE TABLE_SCHEMA='demo'
AND column_type='json'\G
0
Use a database script instead of a csv:
with v (id,bo,c,url) as
(values
( 1,'some text here', 22, cast('[{ "id": "NfKA3", "u": "https://somewebsite.com"}]' as json)),
( 2,'some text here', 83, cast('[{ "id": "W3nAl", "u": "https://somewebsite.com"}]' as json))
)
insert into ...
0
JSON uses double quote " to delimite keys and strings:
[{"id": "NfKA3", "u": "https://somewebsite.com"}]
CSV uses double quote to escape double quotes "" means "
1,"some text here", 22, "[{""id"": ""NfKA3"", ""u"": "&...
1
The parsing consists from 2 stages/queries.
First stage parses root object properties.
WITH
cte1 AS (SELECT @json AS val)
SELECT val->>"$.propertyId.ea" + 0 AS ea,
val->>"$.propertyId.h" AS h,
val->>"$.propertyId.vo" AS vo,
val->>"$.thumbnail.width" + 0 AS t_width,
...
1
You need to unnest the arrays then aggregate everything back into a single JSON value:
select x.value
from the_table t
cross join lateral (
select jsonb_object_agg(k.ky, v.value) as value
from jsonb_array_elements_text(t.keys) with ordinality as k(ky,idx)
join jsonb_array_elements(t.values) with ordinality as v(value,idx) on k.idx = v.idx
)...
0
If category were a regular column and not an expression, PostgreSQL would choose an index-only scan, which would be much faster if the table has been vacuumed recently. But index-only scans are not supported on expression indexes like that, see the documentation:
In principle, index-only scans can be used with expression indexes. For example, given an index ...
5
This may not be the most optimal way to skin the cat but when I need to combine different JSON results in a single output, I typically use the JSON_QUERY() function.
Here is one way to apply it in your case:
SELECT
COLUMN_NAMES = JSON_QUERY(inf_sch.json_result)
, Notification = JSON_QUERY(notif .json_result)
FROM
(
SELECT COLUMN_NAME
FROM ...
0
Amazon Athena database — is there a way to generate an Data Schema for Amazon ML in an automated way
FROM AWS Support-Team:
There are 3 ways of obtaining database schemas through AWS services that I have heard so far. The first two are using Lake Formation, and the last is with glue:
In the Lake Formation console window select Tables, click on the desired table, and hit the properties button, this will show a json schema as part of the information ("...
1
This appears to be a bug in SQL Server's compilation/optimization system.
A feedback item has been submitted:
https://feedback.azure.com/forums/908035-sql-server/suggestions/43341234-json-parsing-error-during-compilation-optimizati
JSON Parsing Error during Compilation / Optimization
Invalid JSON in a parameter can cause an error during the
compilation/...
1
There's another way that doesn't require type casting: json_object_agg(name, value)
test=# create table t (name text, value int);
CREATE TABLE
test=# insert into t values ('key1', 1), ('key2', 2), ('key3', 3);
INSERT 0 3
test=# select * from t;
name | value
------+-------
key1 | 1
key2 | 2
key3 | 3
(3 rows)
test=# select json_object_agg(name,...
1
You need to use the ISJSON() function to verify the column is valid JSON in the SELECT clause regardless of the WHERE clause. The reasoning for this is because contrary to popular belief, sometimes the SQL Engine generates an execution plan where it finds it more efficient to evaluate the SELECT clause before the WHERE clause is applied. That results in ...
1
You need to extract them as text using jsonb_array_elements_text(), then you can cast it to an integer:
select min(x.val::int), max(x.val::int), avg(x.val::int)
from the_table
cross join jsonb_array_elements_text(data #> '{temperature,boiler_temp,values}') as x(val)
4
You are looking for jsonb_set()
update "Assets"
set "Parameters" = jsonb_set("Parameters"::jsonb, '{params2, k}', to_jsonb(2.2))
where ...
4
You can remove given keys with the - operator (the inverse of what you have in mind):
jsonb - text[] → jsonb
Deletes all matching keys or array elements from the left operand.
'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}
But I wouldn't know of a built-in function or operator doing what you ask for in the ...
Top 50 recent answers are included
