Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with json
Search options answers only
not deleted
user 220697
JavaScript Object Notation (JSON) is an open, human and machine-readable standard that facilitates data interchange, and along with XML is the main format for data interchange used on the modern web.
1
vote
JSON - How do I get JSON Child Array into one column - OPENJSON in Synapse
SELECT
p.ProblemType, p.ProblemTypeLang
FROM OPENJSON(@JSONMain, '$.problemtype_data')
WITH (
description nvarchar(max) AS JSON
) ProblemType
CROSS APPLY(
SELECT
ProblemType = STRING_AGG …
- 14.5k
2
votes
Accepted
How can I select data as JSON using a sub query with a string literal?
You could just cast it to json, putting in the obviously missing quotes
concat('{"Id": ', p.id, '}')::json as "provider"
But it's better to use a proper function that does this
json_build_object('Id', …
- 14.5k
2
votes
How to convert json array to json object in PostgreSQL?
json_object_agg(j->>'name', j->>'value')
from json_array_elements(cm.cmt_json_value) j
)
from configuration_matrix cm;
If you only want the data as columns you can just laterally join the JSON …
- 14.5k
4
votes
Nested JSON expressions are converted to strings after a union
You need to put JSON_QUERY on the outer part of a UNION otherwise the compiler doesn't understand that it's already valid JSON. … JSON_QUERY(
ISNULL((
SELECT
ca.Address1,
ca.ContactId
FROM ContactAddress ca
WHERE ca.ContactId = c.Id
for json …
- 14.5k
3
votes
Accepted
Modify a JSON key value based on another key's value in SQL Server
[key]
FROM OPENJSON(mt.settings) array
WHERE JSON_VALUE(array.value, '$.type') = '3'
) j;
db<>fiddle
If you need to change multiple values per row then you need to rebuild the whole JSON. … UPDATE mt
SET settings = '[' + j.json + ']'
FROM ModifiedTable mt
CROSS APPLY (
SELECT
json = STRING_AGG(
IIF(JSON_VALUE(array.value, '$.type') = '3',
JSON_MODIFY(array.value …
- 14.5k
2
votes
Accepted
How to match data against a rules table
You can use jsonpath queries, using the jsonb_path_exists function. See the documentation.
CREATE TABLE YourTable(id int, yourJson jsonb);
CREATE TABLE Rules(id int, rule jsonpath);
INSERT INTO YourT …
- 14.5k
4
votes
Accepted
PgSql jsonb_set for all occurance in json array
The easiest way to do this is to deconstruct the JSON, modify each array element, then rebuild the array
SELECT
(SELECT jsonb_agg(
jsonb_set(j1.value, '{rows}',
(
SELECT … array into separate rows of JSON objects. …
- 14.5k
3
votes
Accepted
Create JSON elements based on table content
Instead you need to build the JSON manually using STRING_AGG and STRING_ESCAPE
SELECT
CONCAT(
'{"ItemId":',
i.ItemId,
',"Name":"',
STRING_ESCAPE(i.Name, 'json'),
'"',
p.Properties … ,
'}'
)
FROM @Item AS i
OUTER APPLY (
SELECT Properties =
STRING_AGG(
',"' +
STRING_ESCAPE(p.Name, 'json') +
'":' +
IIF(ip.ItemId IS NULL, N'false', N'true …
- 14.5k
2
votes
Accepted
Update an nvarchar column with a new json value, without adding quotes to the new value
JSON_QUERY with no path is used to prevent escaping of valid JSON strings. … A text value is not escaped if it is properly formatted JSON produced by FOR JSON, JSON_QUERY, or JSON_MODIFY. …
- 14.5k
0
votes
Accepted
PostgreSQL - How do I get all the values from a JSONB search on a nested object?
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(
s …
- 14.5k
5
votes
Accepted
Return a resultset as JSON text—using pretty formatting
As documented, you can add PRETTY to a JSON function to prettify it
SELECT json_object(* PRETTY)
from dual;
db<>fiddle
Output
{
"DUMMY" : "X"
} …
- 14.5k
0
votes
Unexpected newlines in json output in Postgresql
The TEXT format is specified in the documentation to have each row on a new line. If there are embedded newline characters in the value then these will be encoded with \n.
Text Format
When the text f …
- 14.5k
0
votes
Accepted
How can I query using like on json field in PostgreSQL?
If you store or convert the column to jsonb then you can use JSONpath
SELECT *
FROM polygon_versions
WHERE jsonb_path_exists(
geo_json,
'$.geoJSON.geometry.coordinates[*] ? (@[0] == 30.50410692578 …
- 14.5k
1
vote
Accepted
how to contraint that jsonb column must be a json/object?
You can add the following CHECK constraint
ALTER TABLE YourTable
ADD CONSTRAINT OnlyJsonObject
CHECK (jsonb_typeof(YourColumn) = 'object');
db<>fiddle
See also the documentation.
- 14.5k
1
vote
Accepted
Return Subquery with Multiple Columns as JSON Array of JSON Objects
You can't use jsonb_build_array to build an array out of a set of rows, it only accepts a scalar value. So you need to use jsonb_agg instead, within the subbquery
(
SELECT
jsonb_agg(
…
- 14.5k