Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

New answers tagged

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....
user avatar
  • 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 ...
user avatar
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 '...
user avatar
  • 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 [...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar

Top 50 recent answers are included