New answers tagged json
0
votes
Is it possible to remove a JSON array element by value in MySQL?
I had a syntax error with the above query, but it really helped me sort it out. This worked for me.
UPDATE role AS r
SET permissions = JSON_REMOVE(r.permissions, JSON_UNQUOTE(JSON_search(r....
- 101
0
votes
Accepted
Importing CSV with a json array of strings in one of the column
JSON is a layer of encapsulation with a standardized syntax that the JSON field in your input must respect.
CSV is another layer of encapsulation that applies on top, with a syntax that is more of ...
- 27.2k
1
vote
Accepted
Json extract of ['a','b','c']
SELECT *
FROM mytable
WHERE JSON_CONTAINS('["a","b","c"]', JSON_QUOTE(a_column));
I assume that the column a_column is a string-type column which contains a value like '...
- 17k
2
votes
Accepted
Generate JSON with property names that include dot using SELECT FOR JSON
You can use FOR JSON AUTO which ignores . characters in keys.
In this case, you need to place it in a subquery, otherwise you cannot get the value key
SELECT
[Id],
JSON_QUERY((
SELECT
[...
- 5,214
1
vote
Generate JSON with property names that include dot using SELECT FOR JSON
One solution (in addition to my other answer) is to be a bit more explicit in the JSON structure you wish to generate. Basically, here you are providing FOR JSON with some already properly-parsed ...
- 988
0
votes
Generate JSON with property names that include dot using SELECT FOR JSON
One solution is to create your own escape sequence, and then use procedural T-SQL with @variables, rather than pure relational query language.
Given the inability to directly escape characters in ...
- 988
0
votes
Sql Server - Build a json array of integer from an aggregate
Your string_agg approach is the best I've been able to come up with. It does not seem to be possible to select just an array of scalars. What I've done is to first make a temp table of my data and ...
- 101
3
votes
Accepted
jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"
As the name suggests, jsonb_array_elements() expects a JSON array to unnest. But, according to your error message, at least one row contains a jsonb value in address with a JSON object at the top ...
- 151k
2
votes
jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"
It's pretty trivial in PostgreSQL as well :)
select distinct x.addresses->>'long_name' as country_name
from (
select jsonb_array_elements(t.addresses->'address') as addresses
from foo ...
- 934
Top 50 recent answers are included