Hot answers tagged

4

You could approximate the desired output like this: SELECT p.id , p.[name] , LuckNumbers = ( SELECT ln.LuckyNumber FROM #LuckyNumbers ln WHERE ln.PersonId = p.Id FOR JSON PATH ) FROM #person p FOR JSON PATH; Essentially, creating a JSON array of int values for each person's lucky numbers. ...


3

In PostgreSQL 12, you can obtain JSONB array slice using the jsonb_path_query_array function: SELECT jsonb_path_query_array('["a","b","c","d","e","f"]', '$[2 to 4]'); jsonb_path_query_array ------------------------ ["c", "d", "e"] (1 row)


3

Number one: PostgreSQL will never be great as a key-value store if you have many UPDATEs. Workloads with many UPDATEs are just hard for PostgreSQL's architecture. Make sure that you create your table with a fillfactor way below 100, so that you can make use of HOT updates. This is the only way you can survive workloads with many UPDATEs. Make sure that ...


2

It is not optimized. Each column name, as a JSON key, is sent in full, multiple times. You could try connecting over SSL and turning on compression, but for security reasons that is disabled by default in most systems, and is getting increasingly difficult to turn on. If the column names are really reapeated once per row, we would spend more bandwith ...


2

Keywords for indexing -- Either put it in a TEXT and apply a FULLTEXT index, or put the keywords one-per-row in another table (which is indexed). To critique a schema, we need to see the main SELECTs that you will have.


2

That is an interesting question, so I'll try to give a good answer. In short, the problem is your function definition, which makes unfounded assumptions about the kind of JSON object it has to deal with. Explanation of the error: The error one gets when running your example is not deterministic; it depends on the random numbers in your example. I get this,...


2

You need to combine two jsonb_array_elements() calls: select c.cases ->> 'id' as id, c.cases ->> 'class_name' as class_name from the_table cross join jsonb_array_elements(the_column -> 'suites') as s(suite) cross join jsonb_array_elements(s.suite -> 'cases') as c(cases); Online example


2

jsonb_path_query() is a set-returning function. When putting more than one of those in a SELECT list, this is the expected behavior. See: What is the expected behavior for multiple set-returning functions in SELECT clause? You seem to be looking for a CROSS JOIN instead: SELECT * FROM (SELECT jsonb_path_query(test, '$.title'::jsonpath) AS title FROM ...


2

This has more to do with InnoDB As stated in the documentation to table status The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate ...


2

You can use exists combined with json_array_elements select id from items where exists (select 1 from json_array_elements(labels->'labels') f(x) where x->>'value' in ('peach', 'fruit', 'orange') having count(*)>=2 ); It gives the answer you want, but good luck making it fast if the table is large. (Changing from JSON ...


2

Immediate problem: type json There are no equality or inequality operators defined for the Postgres data type json. See: How to query a json column for empty objects? How to remove known elements from a JSON[] array in PostgreSQL? Hence row comparisons involving a json column are also bound to fail - with notable exceptions, see below. There are (imperfect!...


2

Use NOT EXISTS: SELECT * FROM (VALUES ('id1'), ('id2'), ('id3')) AS v(id) WHERE NOT EXISTS (SELECT FROM content WHERE json_data ->> 'elementId' = v.id); Or if you prefer a join: SELECT v.id FROM (VALUES ('id1'), ('id2'), ('id3')) AS v(id) LEFT JOIN content c ON c.json_data ->> 'elementId' = v.id WHERE c.json_data IS NULL -- or use the PK ...


1

If you really want to be able to add as many fields as you want with no limitation (other than an arbitrary document size limit), consider a NoSQL solution such as MongoDB. For relational databases: use one column per value. Putting a JSON blob in a column makes it virtually impossible to query (and painfully slow when you actually find a query that works). ...


1

Since "37" is a string, not a number, your second query would have to use a jsonb string: ... WHERE p_attribute -> 'age' @> '"37"'::jsonb Note the double quotes around 37. Also note that this query wouldn't be able to use the index, while the first query can.


1

The issue seems to be that SQL is treating the pseudo field from the inner SELECT as text rather than JSON when it has been unioned. When the outer FOR JSON is applied to this pseudo field, it tries to escape the special characters in the text field. See this link for more info. You can negate this using the JSON_QUERY function, essentially forcing SQL ...


1

So with a bit of hunting and adapting, i resolved the issue with a different method. I'll post it here incase someone else has an issue similar to this in the future. The completed route in the server to get the json working correctly is @testRestServer.route("/dbMap", methods=['GET', 'POST']) def getData(): cursor.execute('''SELECT * FROM locations ''') ...


1

jsonb_populate_record can't be used like a table as it only returns a scalar values. I think you want select id, (jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger')).* from my_table mt If the json value doesn't contain the key schrodinger then this will automatically return null Online example


1

Individual PostgreSQL fields over 2000 bytes are automatically compressed, for TOAST storage. However, this compression is not very good, and is local to just that one PostgreSQL value, so can't compress out repetition that occurs between values (like the same keys being used repeatedly, but only once in each JSON structure). You might want to look at ZSON ...


1

Simply no, with a single index on UserID you can easy and fast request the groups and delete also simlpy user from groups. That is all posible with json, but needs much more code and so can cause much more problems. As long you don't need it in your application and also dont change it much, json is good to store a lot of data, but that is xml also.


1

I have a hard time understanding how exactly your JSON values looks like, but I think you want something like this: select ud.* from userdata ud where exists (select * from jsonb_object_keys(ud.data) as t(ky) where t.ky like 'photo%');


1

Faced a similar problem, so I tried the following which worked for me. my schema was something like this, temptable(id serial, events jsonb) and the jsonb array was like this, [{"ts": "t1", "event": "e1", "value": {"search": ["s1"]}}, {"ts": "t2", "event": &...


1

While rereading the documentation, I noticed that whereas the pretty_bool option for JSON doesn't have an indented option, there is a jsonb_pretty(from_json jsonb) function which does produce indented text (Returns from_json as indented JSON text.) which looked as if it is what you are looking for. When you have a "normal" table (I used your DDL in the ...


1

You're doing as well as can be managed with your data structure. The GIN indexes serve the same purpose as any other index - finding rows given a condition. They don't help once the row is located. If you want this to be improved, you'd need to unroll the array elements into separate rows.


1

This has been answered before. The problem is you're reading the top-level JSON only (Products). You need to use CROSS APPLY and read the nested JSON to get all the attributes. See this working example. DECLARE @J NVARCHAR(MAX) = N'{ "products": [ { "description": "Horse shoes", "productid": 1, "orders": [ { "customerid": 101 }, { "...


1

As a primer it looks like the data is being stored with the escape character \ backslash to allow the WordPress server to automatically use the data in the engine itself. You would have to double the back-slashes to find and replace the string. You can verify this via dbfiddle.uk. If you run your Search String against a MySQL 8.0 instance like this: ...


1

Your benchmarking program creates two hash indexes, one to support the constraint, and another free standing one which seems to serve no purpose. Also, 100 iterations is by no means sufficient to draw any conclusions. While 100 independent samples a healthy sample for univariate statistics, these are certainly not independent of each other as they all use ...


1

Instead of looking on the json functions docs page, look instead on the aggregate functions docs page. jsonb_object_agg() will aggregate the object without an additional level of nesting as seen in this demo. select b_d.b_id, jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2)) as b from b_d join d on d.id = b_d.d_id join c on c.id = d....


1

You could extract the values from the JSONB value and aggregate them back into a JSON array select 'some_table' as table_name, id, (select jsonb_agg(x.v) from jsonb_each(to_jsonb(t) - 'id') as x(k,v)) as content from some_table t; But you can't rely that the order of the array elements corresponds to the order of the columns in the table. ...


1

Your case is too dynamic for json_populate_record(): it takes a record type which you don't know (nor have) at the time of calling. PROCEDURE calling UPDATE for each JSON key Proof of concept to show what did not work in your attempt. CREATE PROCEDURE record_event ( foo_arg int, name_arg text, data_arg jsonb, occurred_at_arg timestamptz ) AS $...


1

It would be better to use FILTER (WHERE ...) and COALESCE(..., '{}'::JSON). select json_build_object( 'ID', s.page_id, 'Domain', domain, 'Sections', COALESCE(json_object_agg ( s.title, json_build_object( 'ID', s.sid, 'Texts', t.txts, 'Images', i.imgs ...


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