jsonb column text_results contains values in the following form : {‘Name’ : ‘john doe’ , ‘id’ : ‘123’} On querying Select text_results->>’Name’ as Name from user_master or Select json_extract_path_text(text_results::json,’Name’) as …
Tag: json
How to transform a postgresql select with join to a json object?
I want to transform the result from a select with joins into a json object. I mean this query: should output this: (there are more fields than these. I’m just making the example simple) I found a solution this way: But I think there should be a much better way to do this. Imagine that chat_messages had …
execute powershell from SSMS
I am trying to execute as certain xp_cmdshell code: but this is the error that I get: when I try an execute the same thing in powershell I get the expected response(see image) Powershell The expected output from the xp_cmdshell should be a json Answer The error message implies that your command line is being …
Counting Rows Includes JSON by Id in Codeigniter
We have one groups table name as “groups” (There is mail groups) We have one person table name as “person” (There is persons who has in mail groups) We saved person in the “person” table with json_encode by group ID. Because a person can belong to more than one group. The p…
How to insert an array value to an existing JSON document?
Given a column containing a JSON document, I can use JSON_SET() to set a key in the JSON document to a value. Supported values are null, true, false, numbers and strings; but I can’t figure out how to set an array. The example in the documentation (for JSON_INSERT(), but it works the same) weirdly shows…
How to pass an array of JSON (or JSON array) to pg function to be INSERTed into a Table?
Function to insert rows of json array into a table: Call this function: or this form: or this form: Always received: Answer A JSON array (json) is different from a Postgres array of JSON values (json[]). vs: The first is an array nested inside a single JSON value, the second is an array of JSON values. Postgr…
Turn Cross Apply opensjson results into columns
We have a table that has a field with some json. using cross apply I was able to turn those pairs into columns: So how do I turn this into something like : Thanks. Answer If you know the limited set of possible key names in advance, you can just use PIVOT: But you can’t write a PIVOT if you
Most elegant way to search for a json value and replace it in mysql? [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 1 year ago. Improve this question I have a table called Entity which stores a bunch of jsons. The Json is bas…
How to find out if variable value in database json column Codeigniter?
Im using Codeigniter. Im trying to get some value in database like; the problem is ‘json_value’ column is like = [{“name”:”test1″,”value”:”test1″},{“name”:”test2″,”value”:”test2″}] how can I get ‘…
How to process json data and put it into arrays in postgresql
I have a postgresql table with two columns. name VARCHAR(255) and notes JSON. A sample dataset might look like this: Now I want to do two things. put the list from the json at ‘link_to’ into another column called referrals_to (which must be of array VARCHAR type then). From my example: name notes …