We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

10

If you are strictly confident that your nested arrays will never go deeper than N levels, you can completely unwrap the array-of-arrays with N uses of APPLY. If you need to handle for arbitrary nesting levels, you can unwrap the array-of-arrays recursively using something like the following, which will produce output similar to the following |----|-------|--...


7

SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json); Result: col --- one two


7

This seems to me to be a pretty vanilla recursion query so long as you unwrap the JSON dynamically. If HandyD will excuse some slight plagiarism... declare @Employees nvarchar(max) = '{ "person": "Amy", "staff": [ { "person": "Bill" }, { "person": "Chris", "staff": [ { "person": "Dan" }, { "person": "Emma" } ] ...


6

From JSON_QUERY (Transact-SQL) Returns a JSON fragment of type nvarchar(max). and JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON doesn't escape special characters in the JSON_QUERY return value. So for json path escapes nvarchar(max) data unless the nvarchar(max) data comes from json_query(). In your query the data comes from the ...


6

declare @ex nvarchar(max) = '[ 7,2, [6,7], 2, 10 ]'; ;WITH cte AS ( SELECT * FROM OPENJSON(@ex, '$') AS j1 ) SELECT c.[key], ISNULL(v.value, c.value) FROM cte c OUTER APPLY ( SELECT * FROM OPENJSON(c.value, '$') AS j1 WHERE c.[type] = 4 )v ;


5

The JSON notation definition follows the following schema: The definition of the string is the following: You can see that the quotes are mandatory both at the beginning and at the end. The definition of the value is the following: Note that here you can either supply a string or a number, the number being: Conclusions: Keys must have quotes both at ...


5

As per the documentation the OPENJSON command requires compatibility mode 130 - this is SQL Server 2016 In this case this means that the feature was introduced in SQL Server 2016, and can only be used in databases where the Compatibility model is set to 130 (SQL Server 2016) or higher. Compatibility levels are tied to a specific SQL Server version and are ...


4

You need to first unnest the array elements, and then aggregate back each value: select id, (select jsonb_agg(t -> 'a') from jsonb_array_elements(record) as x(t)) as record from the_table; Online example: https://rextester.com/ZONHTW97204


4

You can unnest json array: Postgres WITH ORDINALITY: When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest(). Have a look at this answer of Erwin ...


4

First you do something like this, SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j FROM foo GROUP BY id; That aggregates the values into an JSON Array. You get id | j ----+---------------------- 2 | [[56, 67], [58, 64]] 1 | [[34, 45], [45, 56]] From there you need to build an JSON Object.. SELECT jsonb_object_agg(id,j) FROM ...


3

What better way than to test? TL:DR up front. The way you structure your data makes it awkward when there are multiple conditions, as you have to check different rows for the same response. Columnstore works better simply for using batch mode in the self join of my test query. I would expect you to gain far more from structuring your data in a different ...


3

So it is using the jsonb index. But your work_mem is not large enough to hold the full bitmap, so it goes "lossy" resulting in extra work, see: Rows Removed by Index Recheck: 1434250 Heap Blocks: exact=42839 lossy=99688 Increasing the size of your work_mem setting should improve this. Your functional index cannot be used, because of a mismatch ...


3

You can use OPENJSON and a CTE to extract each person and their associated staff: declare @Employees nvarchar(max) = '{ "person": "Amy", "staff": [ { "person": "Bill" }, { "person": "Chris", "staff": [ { "person": "Dan" }, { "person": "Emma" } ] } ] }'; ;WITH Level1 AS ( SELECT 1 AS Level, NULL AS ...


3

It sounds like you are using the email address as a key, which is not good practise. Anything that can change is not a good candidate to be a key value which includes email addresses and phone numbers. Other reasons it is not a good idea include: It could be NULL: maybe there are people in your target audience who don't use email so won't have an address? ...


2

For an array of JSON objects (one object per row in query), you can do this: SELECT JSON_ARRAYAGG(JSON_OBJECT("fieldA", fieldA, "fieldB", fieldB)) FROM table; It would result in a single JSON array containing all entries: [ { "fieldA": "value", "fieldB": "value" }, ... ] Unfortunately, MySQL does not allow for selecting all fields with *....


2

This will give you the same answer, but a lot easier code. I just used json_object with group_concat to simplify the other answer. select concat('[', GROUP_CONCAT( JSON_OBJECT( 'name_field', name_field ,'address_field', address_field ,'contact_age', contact_age ) ...


2

This is not a trivial task. Assuming the two nested JSON arrays reliably exist in every row, this works: UPDATE forms f SET form = u.form1 FROM ( SELECT f.id, f.form || jsonb_build_object('widgets', jsonb_agg(w.widget1)) AS form1 FROM forms f CROSS JOIN LATERAL ( SELECT w.widget || p.permissions AS widget1 FROM ...


2

You will need to measure the two ways against your performance requirements. That said, in the world of RDBMS (which Postgresql is), generally it's better to keep tables rather than JSON/XML/Blobs etc. The databases are optimised and designed for relational data and while it's true they have supporting features for JSON/XML etc, often these are not as ...


2

Neither. What you need is a file system, or cloud blob storage. Choose one with high compression ratios. Typically data is used intensely for a short while then touched only infrequently thereafter. Choosing a storage service with tiers will allow further cost saving. As the service becomes popular it is likely several users will have the same dataset. ...


2

This can't be done. Of the two built-in GIN operators for indexing JSONB, one of them only stores hashed values, so you wouldn't be able to reverse them, and other one stores flattened keys, irrespective of what level of the JSONB they were at. That second one also hashes the values if they exceed a certain length. Both of the methods can lead to false ...


2

This is all around a horrible schema. You shouldn't be using json (as compared with jsonb) at all, ever (practically). If you're querying on the field, it should be jsonb. In your case, that's still a bad idea though, you likely want an sql array.. CREATE TABLE raw ( raw_id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, data int[] ); ...


2

You need to "unnest" the elements of the JSON array and then join that to your media table: The following query assumes that media is a column of type jsonb. If that is not the case (which it should be) you need to cast it media::jsonb. select m.id, m.path from media m join ( select jm.id::integer as media_id from playlist p cross join ...


2

If the file contains a valid JSON literal, you could read it in with pg_read_file() and assign to a json variable directly: CREATE OR REPLACE FUNCTION file_read(file text) RETURNS void AS $func$ DECLARE content json := pg_read_file(file, 0, 10000000); -- arbitrary max. 10 MB BEGIN -- do some more stuff here END $func$ LANGUAGE plpgsql; But that ...


2

I recommend you collect the queries that users use. Periodically go through them to see what json 'columns' are most commonly filtered on and build indexes (with Virtual, etc). Be sure to also look for pairs of columns that are filtered on. In this case, be sure to put the = column first. In your example, each of the filters is a 'range', so there is no ...


2

I don't think the archeological approach will be very useful here. There is just too much missing info and confounding variables. For example, people usually don't just add indexes for no reason. If a change in work load motivated the index creation, it could be the change in workload, independent of the index, which is causing the bloat. There are a lot ...


2

Try using OPENJSON instead. This returns a Type column that indicates a NULL value for a key. You can LEFT JOIN this to a source list of possible keys and check for a NULL Type value or a NULL return value to determine if the key is present or not. Example below: declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}'; SELECT [Value], ...


2

There is a system function for this purpose called STRING_ESCAPE that will escape the characters as needed for a given string type. In your example: declare @data nvarchar(max) = N'"TEST"'; declare @jsonFragment nvarchar(max); declare @id int = 999; set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + STRING_ESCAPE(@data, 'json') + '"'; select @...


2

Unnest the array, then aggregate back: select string_agg(city, ',') from ( select x.val ->> 'Name' as city from the_table t cross join jsonb_array_elements(t.the_column -> 'Cities') as x(val) ) t; If you have a bigger query, use that in a derived table: select string_agg(t2.city, ',') from ( select x.val ->> 'Name' as city ...


2

You could opt to use TOP(1) in the CROSS APPLY SELECT Id FROM Journals journals CROSS APPLY ( SELECT TOP(1) Sensitive FROM OPENJSON(journals.Data) WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') WHERE Sensitive = 1 ) as jsonValues; Or, as you mentioned DISTINCT SELECT DISTINCT Id FROM Journals journals CROSS APPLY OPENJSON(journals....


2

No plans that I know about. Regarding constraints, I assume you mean for JSON validation: See Changes and Improvements in MariaDB 10.4: The JSON_VALID function is automatically used as a CHECK constraint for the JSON data type alias in order to ensure that a valid json document is inserted (MDEV-13916) ... so you don't have to set the constraint ...


Only top voted, non community-wiki answers of a minimum length are eligible