`json` is basically a blob that stores JSON data in raw format, preserving even insignificant things such as whitespace, the order of keys in objects, or even duplicate keys in objects. It does offer the ability to do some basic JSON operations such as extracting the value associated with some key in an object, albeit it is slow at that since it has to parse the JSON blob every time. It also validates every value to check that it is valid JSON. `jsonb` on the other hand stores JSON data in a custom format that is optimized for certain operations such as extracting the value associated with some key in an object (i.e. it will not reparse JSON, it will not search linearly). Additionally, `jsonb` supports more operations, just as concatenation of objects or setting a value deep inside an object.

In general, I use `json` only if I know that I will not do any JSON operations or only do them occasionally. For all other cases I use `jsonb`. Note that for the former case, `text` it is also a perfectly valid option, especially if you are not interested in the validation that `json` does (e.g. because you trust the source of the data).

This is explain:
https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/

&gt; In most cases JSONB is likely what you want when looking for a NoSQL,
&gt; schema-less, datatype. Hstore and JSON can have their place as well
&gt; but it’s less common. More broadly, JSONB isn’t always a fit in every
&gt; data model. Where you can normalize there are benefits, but if you do
&gt; have a schema that has a large number of optional columns (such as
&gt; with event data) or the schema differs based on tenant id then JSONB
&gt; can be a great fit. In general you want:
&gt; 
&gt;     JSONB - In most cases
&gt;     JSON - If you’re just processing logs, don’t often need to query, and use as more of an audit trail
&gt;     hstore - Can work fine for text based key-value looks, but in general JSONB can still work great here



Following code was perfectly worked with old swift. This is an extension of String

    
    func stringByConvertingHTML() -&gt; String {
        let newString = replacingOccurrences(of: &quot;\n&quot;, with: &quot;&lt;br&gt;&quot;)
        if let encodedData = newString.data(using: String.Encoding.utf8) {
            let attributedOptions : [String: AnyObject] = [
                NSDocumentTypeDocumentAttribute: NSHTMLTextDocumentType as AnyObject,
                NSCharacterEncodingDocumentAttribute: String.Encoding.utf8 as AnyObject
            ]
            do {
                let attributedString = try NSAttributedString(data: encodedData, options: attributedOptions, documentAttributes: nil) //Crash here
                return attributedString.string
            } catch {
                return self
            }
        }
        return self
    }


But in swift 3 it crashes saying 

&gt;  *** Terminating app due to uncaught exception
&gt; &#39;NSInvalidArgumentException&#39;, reason: &#39;-[_SwiftValue
&gt; unsignedIntegerValue]: unrecognized selector sent to instance
&gt; 0x6080002565f0&#39;

Anyone please suggest me what need to do?

Swift-3 error: &#39;-[_SwiftValue unsignedIntegerValue]: unrecognized selector

Can someone briefly explain to me the difference in use between the methods `uniq` and `distinct`?

I&#39;ve seen both used in similar context, but the difference isnt quite clear to me.

Rails: uniq vs. distinct

What&#39;s difference between JSON and JSONB data type in PosgresSQL?

1. When should be used specific one? 
2. What&#39;s benefits or disadvantages with respect to other?

Difference between JSON and JSONB in Postgres

<p>What's difference between JSON and JSONB data type in PosgresSQL?</p>
<ol>
<li>When should be used specific one?</li>
<li>What's benefits or disadvantages with respect to other?</li>
</ol>


I&#39;m new to React and I&#39;m trying to import a JSON `DATA` variable from an external file. I&#39;m getting the following error: 

&gt; Cannot find module &quot;./customData.json&quot;

Could some one help me? It works if I have my `DATA` variable in `index.js` but not when it&#39;s in an external JSON file.

#### index.js

    import React, {Component} from &#39;react&#39;;
    import ReactDOM from &#39;react-dom&#39;;
    import customData from &#39;./customData.json&#39;;
    import Profile from &#39;./components/profile&#39;;
    import Hobbies from &#39;./components/hobbies&#39;;

    class App extends Component {
	  render() {
        return (
          &lt;div&gt;
            &lt;Profile name={this.props.profileData.name}imgUrl={this.props.profileData.imgURL} /&gt;
            &lt;Hobbies hobbyList={this.props.profileData.hobbyList}/&gt;
          &lt;/div&gt;
        );
      }
    }

    ReactDOM.render(&lt;App profileData={DATA}/&gt;, document.querySelector(&#39;.container&#39;));

#### hobbies.js

    import React, {Component} from &#39;react&#39;;

    var Hobbies = React.createClass({
      render: function(){
        var hobbies = this.props.hobbyList.map(function(hobby, index){
            return (&lt;li key={index}&gt;{hobby}&lt;/li&gt;);
        });
        return (
        	&lt;div&gt;
	        	&lt;h5&gt;My hobbies:&lt;/h5&gt;
	            &lt;ul&gt;
	            	{hobbies}
	            &lt;/ul&gt;
            &lt;/div&gt;
        );
      } 
    });

    export default Hobbies;


#### profile.js

    import React from &#39;react&#39;;

    var Profile = React.createClass({
	render: function(){
		return (
			&lt;div&gt;
				&lt;h3&gt;{this.props.name}&lt;/h3&gt;
				&lt;img src={this.props.imgUrl} /&gt;
			&lt;/div&gt;
		)
	  }
    });

    export default Profile

#### customData.json

    var DATA = {    
        name: &#39;John Smith&#39;,
        imgURL: &#39;http://lorempixel.com/100/100/&#39;,
        hobbyList: [&#39;coding&#39;, &#39;writing&#39;, &#39;skiing&#39;]
    }

    export default DATA



Import JSON file in React

In Python, what is the difference between `json.load()` and `json.loads()`?

I guess that the *load()* function must be used with a file object (I need thus to use a context manager) while the *loads()* function take the path to the file as a string. It is a bit confusing.

Does the letter &quot;**s**&quot; in `json.loads()` stand for **string**?

Thanks a lot for your answers!

What is the difference between json.load() and json.loads() functions

For:

    bodyParser.urlencoded({extended: ...})

my research shows me that if `extended: true`, then you can parse nested objects, or generally any type. However, if you set `extended: false`, then you can only parse strings or arrays. But what does ...

    app.use(bodyParser.json())

mean exactly? I mean, yes... I know the docs mention that it parses json. But I am still confused. I have noticed applications that set `extended: true` do NOT use `bodyParser.json()` at all. But applications that `extended: false` tend to use `bodyParser.json()`. Why is this? At the end of the day, both applications are able to parse json.

Secondly, which is the recommended approach?

What does `app.use(bodyParser.json())` do?

I have a dataframe `df` that loads data from a database. Most of the columns are json strings while some are even list of jsons. For example:

    id     name     columnA                               columnB
    1     John     {&quot;dist&quot;: &quot;600&quot;, &quot;time&quot;: &quot;0:12.10&quot;}    [{&quot;pos&quot;: &quot;1st&quot;, &quot;value&quot;: &quot;500&quot;},{&quot;pos&quot;: &quot;2nd&quot;, &quot;value&quot;: &quot;300&quot;},{&quot;pos&quot;: &quot;3rd&quot;, &quot;value&quot;: &quot;200&quot;}, {&quot;pos&quot;: &quot;total&quot;, &quot;value&quot;: &quot;1000&quot;}]
    2     Mike     {&quot;dist&quot;: &quot;600&quot;}                       [{&quot;pos&quot;: &quot;1st&quot;, &quot;value&quot;: &quot;500&quot;},{&quot;pos&quot;: &quot;2nd&quot;, &quot;value&quot;: &quot;300&quot;},{&quot;pos&quot;: &quot;total&quot;, &quot;value&quot;: &quot;800&quot;}]
    ...

As you can see, not all the rows have the same number of elements in the json strings for a column. 

What I need to do is keep the normal columns like `id` and `name` as it is and flatten the json columns like so:

    id    name   columnA.dist   columnA.time   columnB.pos.1st   columnB.pos.2nd   columnB.pos.3rd     columnB.pos.total
    1     John   600            0:12.10        500               300               200                 1000 
    2     Mark   600            NaN            500               300               Nan                 800 

I have tried using `json_normalize` like so:

    from pandas.io.json import json_normalize
    json_normalize(df)

But there seems to be some problems with `keyerror`. What is the correct way of doing this?

How to flatten a pandas dataframe with some columns as json?


**UPDATE:** This is now possible in MySQL 8 via the JSON_TABLE function: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

I&#39;m loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a normal table structure.

Grabbing JSON, manipulating and extracting arrays from it etc. is simple. JSON_EXTRACT all the way. But what about the inverse, going from a JSON array to rows? Perhaps I am dense on the existing MySQL JSON functionality, but I haven&#39;t been able to figure that one out.

For example, say I have a JSON array and want to insert a row for each element in the array with its value? The only way I have found is to write a bunch of JSON_EXTRACT(... &#39;$[0]&#39;) JSON_EXTRACT(... &#39;$[1]&#39;) etc and union them together.

Or, say I have a JSON array and want to GROUP_CONCAT() it to a single comma separated string?

In other words, I know I can do this:

    SET @j = &#39;[1, 2, 3]&#39;;
    SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT(&#39;$[&#39;, x.n, &#39;]&#39;))) AS val
      FROM   
      (    
        SELECT 0 AS n    
        UNION    
        SELECT 1 AS n    
        UNION    
        SELECT 2 AS n    
        UNION    
        SELECT 3 AS n    
        UNION    
        SELECT 4 AS n    
        UNION    
        SELECT 5 AS n    
      ) x
    WHERE x.n &lt; JSON_LENGTH(@j);

But that hurts my eyes. And my heart.

How can I do something like:

    SET @j = &#39;[1, 2, 3]&#39;;
    SELECT GROUP_CONCAT(JSON_EXTRACT(@j, &#39;$[ * ]&#39;))

... and have it concatenate together the values in the array vs. the JSON array itself?

I guess what I&#39;m looking for here is some sort of JSON_SPLIT along the lines of:

    SET @j = &#39;[1, 2, 3]&#39;;
    
    SELECT GROUP_CONCAT(val)
    FROM
      JSON_SPLIT(JSON_EXTRACT(@j, &#39;$[ * ]&#39;), &#39;$&#39;)

If MySQL had a proper STRING_SPLIT(val, &#39;separator&#39;) table returning function, I could hack it (escaping be damned), but that&#39;s not available either.



Convert JSON array in MySQL to rows

I&#39;m sure this is a duplicate question in the sense that the answer is out there somewhere, but I haven&#39;t been able to find the answer after Googling for 10 minutes, so I&#39;d appeal to the editors not to close it on the basis that it might well be useful for other people. 

I&#39;m using Postgres 9.5. This is my table:

            Column          │           Type            │                                Modifiers
    ─────────────────────────┼───────────────────────────┼─────────────────────────────────────────────────────────────────────────
     id                      │ integer                   │ not null default nextval(&#39;mytable_id_seq&#39;::regclass)
     pmid                    │ character varying(200)    │
     pub_types               │ character varying(2000)[] │ not null

I want to find all the rows with &quot;Journal&quot; in `pub_types`.

I&#39;ve found the docs and googled and this is what I&#39;ve tried:

    select * from mytable where (&quot;Journal&quot;) IN pub_types;
    select * from mytable where &quot;Journal&quot; IN pub_types;
    select * from mytable where pub_types=ANY(&quot;Journal&quot;);
    select * from mytable where pub_types IN (&quot;Journal&quot;);
    select * from mytable where where pub_types contains &quot;Journal&quot;;

I&#39;ve scanned [the postgres array docs][1] but can&#39;t see a simple example of how to run a query, and StackOverflow questions all seem to be based around more complicated examples. 



  [1]: https://www.postgresql.org/docs/9.1/static/arrays.html

Postgres: check if array field contains value?

I&#39;ve been looking for a solution for this and could not find a working solution.

I&#39;ve installed postgres using brew (`brew install postgres`)  in my MacBook and I am currently running it using brew services (`brew services list`  displays postgres as a running service). However, when I try to run `psql` I get following error.

&gt; psql: could not connect to server: No such file or directory 	Is the
&gt; server running locally and accepting 	connections on Unix domain
&gt; socket &quot;/tmp/.s.PGSQL.5432&quot;?

Anyone has already solved similar problem?

Cannot connect to Postgres server running through brew services

In my PostgreSQL database I have 2 users: postgres and myuser.

The default user is postgres, but this user has no permission to query my foreign tables and myuser does. How can I check if I&#39;m connected with the right user?

If I&#39;m using the wrong user, how do I change to the right one?

How to check connected user on psql

I&#39;m no Homebrew expert but I think it has &quot;upgraded&quot; me from readline version 6.x to 7.0 sometime after [9/15/16][1]:

    eat@eric-macbook:Homebrew$ brew info readline
    readline: stable 7.0 (bottled) [keg-only]
    Library for command-line editing
    https://tiswww.case.edu/php/chet/readline/rltop.html
    /usr/local/Cellar/readline/7.0 (45 files, 2M)

This has caused headaches for my 9.4.5 Homebrew version of Postgresql (I need the older 9.4 for comparability reasons):

    eat@eric-macbook:~$ psql --version
    dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.dylib
      Referenced from: /usr/local/Cellar/postgresql/9.4.5/bin/psql
      Reason: image not found
    Trace/BPT trap: 5

Unfortunately I can&#39;t find a 6.x version of readline on Homebrew to revert to - only 7.0 seems to be available(?).

My question is twofold:

 - Is the the readline version mismatch the cause of my postgres/psql problem?
 - If so, how do I return to 6.x with Homebrew to correct the problem?

Thank you in advance!


  [1]: http://ftp.gnu.org/gnu/readline/

Return to readline version 6.x in Homebrew to fix Postgresql?

When using Psql in Linux, if the result of my SQL query contains many columns or long strings of data, it will wrap the initial view and only once I scroll to the side will it stop wrapping and show each row on a separate line.

I&#39;ve tried various [`\pset` options][1] such as `format unaligned`, `format aligned`, `format wrapped`, `columns 0`, `columns 1000`, but none seemed to *fully* stop wrapping unless I generate static output to a file.

How can I set it to never wrap the output while still being scrollable and showing the result using the default ascii table format?


  [1]: https://www.postgresql.org/docs/9.2/static/app-psql.html

Disable wrapping in Psql output

I am new to DynamoDB. I wish to create a table which using DeviceID as the hash key, Timestamp as my range key and some data.

    { DeviceID: 123, Timestamp: &quot;2016-11-11T17:21:07.5272333Z&quot;, X: 12, Y: 35 }

In SQL, we can use datetime type for Timestamp, but in DynamoDB there is none. 

1. What data type should I use? String? Number?  
[![enter image description here][1]][1]

2. For the chosen data type, what kind of timestamp format should I write in? ISO format (e.g: 2016-11-11T17:21:07.5272333Z) or epoch time (e.g: 1478943038816)?  

3. I need to search through the table through a range of time, e.g: 1/1/2015 10:00:00am until 31/12/2016 11:00:00pm


  [1]: https://i.stack.imgur.com/DjTuN.png

What data type should be used for timestamp in DynamoDB?

I have some clients getting weird bills. I was able to isolate the core problem:

    SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 200 what the?
    SELECT 199.96 - (0.0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
    SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96)) -- 199.96

    SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
    SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4))))                         -- 199.96
    SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96))                         -- 199.96

    -- It gets weirder...
    SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 0
    SELECT (0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4))))                         -- 0
    SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96))                         -- 0

    -- so... ... 199.06 - 0 equals 200... ... right???
    SELECT 199.96 - 0 -- 199.96 ...NO....

Has anyone a clue, what the heck is happening here? I mean, it has certainly something to do with the decimal datatype, but I can&#39;t really wrap my head around it...

------------------------------------------

There was a lot of confusion about of what datatype the number literals were, so I decided to show the real line:

    PS.SharePrice - (CAST((@InstallmentCount - 1) AS DECIMAL(19, 4)) * CAST(FLOOR(@InstallmentPercent * PS.SharePrice) AS DECIMAL(19, 4))))

    PS.SharePrice DECIMAL(19, 4)

    @InstallmentCount INT

    @InstallmentPercent DECIMAL(19, 4)

I made sure that the result of each operation having an operand of a type different than `DECIMAL(19, 4)` is cast explicitly before applying it to the outer context.

Nevertheless, the result remains `200.00`.

------------------

I have now created a boiled down sample you guys can execute on your computer.

    DECLARE @InstallmentIndex INT = 1
    DECLARE @InstallmentCount INT = 1
    DECLARE @InstallmentPercent DECIMAL(19, 4) = 1.0
    DECLARE @PS TABLE (SharePrice DECIMAL(19, 4))
    INSERT INTO @PS (SharePrice) VALUES (599.96)

    -- 2000
    SELECT
      IIF(@InstallmentIndex &lt; @InstallmentCount,
      FLOOR(@InstallmentPercent * PS.SharePrice),
      1999.96)
    FROM @PS PS

    -- 2000
    SELECT
      IIF(@InstallmentIndex &lt; @InstallmentCount,
      FLOOR(@InstallmentPercent * CAST(599.96 AS DECIMAL(19, 4))),
      1999.96)
    FROM @PS PS

    -- 1996.96
    SELECT
      IIF(@InstallmentIndex &lt; @InstallmentCount,
      FLOOR(@InstallmentPercent * 599.96),
      1999.96)
    FROM @PS PS

    -- Funny enough - with this sample explicitly converting EVERYTHING to DECIMAL(19, 4) - it still doesn&#39;t work...
    -- 2000
    SELECT
      IIF(@InstallmentIndex &lt; @InstallmentCount,
      FLOOR(@InstallmentPercent * CAST(199.96 AS DECIMAL(19, 4))),
      CAST(1999.96 AS DECIMAL(19, 4)))
    FROM @PS PS

-------------

Now I&#39;ve got something...

    -- 2000
    SELECT
      IIF(1 = 2,
      FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))),
      CAST(1999.96 AS DECIMAL(19, 4)))

    -- 1999.9600
    SELECT
      IIF(1 = 2,
      CAST(FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) AS INT),
      CAST(1999.96 AS DECIMAL(19, 4)))

What the hell - floor is supposed to return an integer anyway. What&#39;s going on here? :-D

------------

I think I now managed to really boil it down to the very essence :-D

    -- 1.96
    SELECT IIF(1 = 2,
      CAST(1.0 AS DECIMAL (36, 0)),
      CAST(1.96 AS DECIMAL(19, 4))
    )

    -- 2.0
    SELECT IIF(1 = 2,
      CAST(1.0 AS DECIMAL (37, 0)),
      CAST(1.96 AS DECIMAL(19, 4))
    )

    -- 2
    SELECT IIF(1 = 2,
      CAST(1.0 AS DECIMAL (38, 0)),
      CAST(1.96 AS DECIMAL(19, 4))
    )


Why is 199.96 - 0 = 200 in SQL?

[*For versions less than 9.5 see this question*](https://stackoverflow.com/q/30707482/124486)
----

I have created a table in PostgreSQL using this:

    CREATE TEMP TABLE jsontesting
    AS
      SELECT id, jsondata::jsonb FROM ( VALUES
        (1, &#39;[&quot;abra&quot;,&quot;value&quot;,&quot;mango&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (2, &#39;[&quot;japan&quot;,&quot;china&quot;,&quot;india&quot;, &quot;russia&quot;, &quot;australia&quot;]&#39;),
        (3, &#39;[&quot;must&quot;, &quot;match&quot;]&#39;),
        (4, &#39;[&quot;abra&quot;,&quot;value&quot;,&quot;true&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (5, &#39;[&quot;abra&quot;,&quot;false&quot;,&quot;mango&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (6, &#39;[&quot;string&quot;,&quot;value&quot;,&quot;mango&quot;, &quot;apple&quot;, &quot;sample&quot;]&#39;),
        (7, &#39;[&quot;must&quot;, &quot;watch&quot;]&#39;)
      ) AS t(id,jsondata);

Now what I wanted was to 

  * **add** Something like *append_to_json_array* takes in the actual jsondata which is a json-array and the newString which I have to add to that jsondata array and this function should return the updated json-array. 

        UPDATE jsontesting
        SET jsondata=append_to_json_array(jsondata, &#39;newString&#39;)
        WHERE id = 7;

  * **remove** a value from the json data array, one function for removing the value.

I tried to search documentation of PostgreSQL but found nothing there.

Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+

I have the following table called module_data.  Currently it has three rows of entries:

					id                               data
	0ab5203b-9157-4934-8aba-1512afb0abd0 {&quot;title&quot;:&quot;Board of Supervisors Meeting&quot;,&quot;id&quot;:&quot;1i3Ytw1mw98&quot;}
	7ee33a18-63da-4432-8967-bde5a44347a0 {&quot;title&quot;:&quot;Board of Supervisors Meeting&quot;,&quot;id&quot;:&quot;4-dNAg2mn6o&quot;}
	8d71ca35-74eb-4751-b635-114bf04843f1 {&quot;title&quot;:&quot;COPD 101&quot;, &quot;id&quot;:&quot;l9O0jCR-sxg&quot;}

Column data&#39;s datatype is `jsonb`.  I&#39;m trying to query it using `like` operator. Something like the following:

    SELECT * FROM module_data WHERE title LIKE &#39;%Board%&#39;;

I&#39;ve been looking at the `jsonb` support and there doesn&#39;t seem to be a `like` operator. If anyone has any advice.

Content Type	Original Author	Original Content on Stackoverflow
Question	Somnath Muluk	View Question on Stackoverflow
Solution 1 - Json	redneb	View Answer on Stackoverflow
Solution 2 - Json	Piotr Rogowski	View Answer on Stackoverflow

Difference between JSON and JSONB in Postgres

Json Problem Overview

Json Solutions

Solution 1 - Json

Solution 2 - Json

Rails: uniq vs. distinct

Swift-3 error: '-[_SwiftValue unsignedIntegerValue]: unrecognized selector

Attributions