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 …
Tag: json
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-…
Expand a postgres array of JSON within a single column into multiple records
I have a table called goals where each row contains 1 goal. The data about specific metrics for the goal is stored in ARRAY of JSON called goal_metrics. Sometimes there is 1 metric, sometimes there …
How to search JSON data associative array mysql 5.5
I have stored JSON associative array data in the column of the table in MySQL 5.5. I want to search in JSON at one specific array key with key and value my JSON structure : So for search by Key Value, I have this query : It is working fine but my problem is that I can not select Interface_1
SQL Server query JSONobject to get aggregated values
I have a JSON object stored in SQL Server, in a nvarchar(max) column. The JSON looks like this: { “data”: [{ “RespID”: 3512, “ObsPriceValue”: 2.34 }, { “RespID”: 4904, …
How to get particular object from jsonb in PostgreSQL?
I have a table called ‘Test’ that holds two fields ‘qnId’ and ‘Answers’. ‘qnId’ stores a uuid and ‘Answers’ is a jsonb array that roughly looks like this: [{ “user” : “1”, “ans” : “some text” }, { “…
Return Table Object from Stored JSON in a Column
I am sorry, I know this has been asked a billion times but I have tried to put together CROSS APPLY statements without any luck and needs some help. I have this data in SQL in a single column which I …
How to Group By some id and Sum all row json encode data
I have Faced group by some id and Sum other column json_encode data in select query MySQL. But how to sum total json data value? Please help Anyone… Using this, PHP 7.2, Mysql 5 and Apache 2. …