I want to filter rows that have gold badges. I am using PG12 and I use new path feature of it. To clarify, I have a users_tbl table like this: CREATE TABLE users_tbl ( ID serial NOT NULL PRIMARY …
Tag: json
MySQL – removal of duplicate results in JSON_ARRAYAGG in the presence of GROUP BY
I have query: The result of this query duplicates the same image urls in column urlLinks: How can I leave as a result only unique image urls? GROUP BY cannot be removed from the request!!! Answer JSON_ARRAYAGG() does not support DISTINCT. You can SELECT DISTINCT in a subquery, and then aggregate: Demo on DB Fiddle: id | urlLinks —–: |
Using Classic ASP – SQL – JSON
I’m trying to serialize a SQL query in Classic ASP to a json object and I have found some great code that helps me achieve that but where my issue comes in is I then need to manipulate the output that is returned by the QueryToJSON(cmd, arParams).Flush I need to take the value and add some string / text to
Aggregation of Json objects on a relational table on Postgres
I have a relational table that contains three variable values, an ID, and the date broken down into parts. ID | Year | Month | Day | A | B | C | 1 2019 12 01 5 10 15 1 2019 …
LIMIT and Contains in a single JSONB Query
I have 2 queries that separately run perfectly: This one limits the results: SELECT jsonb_agg(elem) as data FROM ( SELECT * FROM file_data, jsonb_array_elements(file_data) a(elem) LIMIT 3 ) …
Find rows based on nested key in jsonb array
I have a jsonb column in Postgres 9.6 that contains JSON arrays of the following form: [ { “courses”: { “course-1”: { “graduated”: false }, “course-5”: { “graduated”: true } } }, { “…
JSONB sort aggregation
I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document. (Fake, generated random data) SELECT jsonb_agg(elem) FROM ( SELECT * FROM …
GROUP_CONCAT to return an object array in a many to many relationship
I am working on a mySQL query for a personal project. Right now the end goal is to have the query return the following json for each item: My most recent attempt is: Which is giving me an internal server error. Testing it with a simplier query goes through fine so I’ve narrowed it down to the query itself being
Correct json formatting in ms sql server
I have the following SQL table I wrote this code to format result as JSON, but can’t get it in the desired format. If i name all product columns as name then sql returns an error Use different names and aliases for each column in SELECT list. Sql code: Current output: Desired output: Answer You may try with the next
Query JSONB column for any value where =?
I have a jsonb column which has the unfortunate case of being very unpredictable, in some cases its value may be an array with nested values: [“UserMailer”, “applicant_setup_3”, [“5cbffeb7-8d5e-4b52-…